Hash or Array solution ?

[AshokD]

Hi All,

I need some guidelines to proceed with my requirement. Here are the details:-

#1)  I have a input txt file data as below:-

Row1 -->123456 Transact1 Transact2 Transact3

Row2--> 456789 Transac1  Transact2

Note :  We don't know about the position of the data in the file. Numbers in the first position are the employee ids and followed by transaction types.

I have to read the input file and create a dataset "InptData" with two columns and it's corresponding vaues. How we can read this ?

EmployeeID TransactionType

#2) I have queried from database and created another dataset 'Details' with below values:-

TransactionType  Service   

Transact1                A1        

Transact2                A2

Transact3               A1

I have to merge  #1 and #2 datasets to determine the Service for each transactions for the employee

.Once we determined the service ,I have to output the data to different fles:-

If service is A1 then output to File1

If service is A2 then output to File2

Could you please someone guide me on the SAS functionality for this requirement ?

Thanks in advance for your help .

[Reeza]

Neither of these need hash or array. A data step should work. 

@Some hints, look up the trailing @@ or _infile_ for number 1. 

For your second question look up the OUTPUT statement. 

---

@AshokD wrote:

Hi All,

I need some guidelines to proceed with my requirement. Here are the details:-

#1)  I have a input txt file data as below:-

Row1 -->123456 Transact1 Transact2 Transact3

Row2--> 456789 Transac1  Transact2

Note :  We don't know about the position of the data in the file. Numbers in the first position are the employee ids and followed by transaction types.

I have to read the input file and create a dataset "InptData" with two columns and it's corresponding vaues. How we can read this ?

EmployeeID TransactionType

#2) I have queried from database and created another dataset 'Details' with below values:-

TransactionType  Service   

Transact1                A1        

Transact2                A2

Transact3               A1

I have to merge  #1 and #2 datasets to determine the Service for each transactions for the employee

.Once we determined the service ,I have to output the data to different fles:-

If service is A1 then output to File1

If service is A2 then output to File2

Could you please someone guide me on the SAS functionality for this requirement ?

Thanks in advance for your help .

---

[AshokD]

Thanks @ChrisNZ

I hope this will work but need a small clarification.

I'm having nearly 100+ transaction types so can you please suggest logic using array or hash instead of merge step so that it performs faster?

Please correct me if I am wrong.

[ChrisNZ]

If performance is an issue I would create formats from the details table and apply them in the very first data step. This way you only have one step.

[AshokD]

@ChrisNZ @Kurt_Bremser @Reeza

Thanks for the inputs and suggestion.

I will try both the methods and let's see which one suits.

Thanks for your time and help.

[AshokD]

@ChrisNZ @KurtBremser @Reeza

Hi All,

Using proc format method is working fine but the requirement is need in hash table lookup.

Could you please suggest a logic on hash table ?

Thanks in advance for your help.

[Kurt_Bremser]

You don't need a merge at all, you do it in one data step with a format:

data cntlin;
input start :$20. label $;
fmtname = "transacts";
type = 'C';
cards;
Transact1 A1
Transact2 A2
Transact3 A1
;
run;

proc format lib=work cntlin=cntlin;
run;

data
  a1
  a2
;
infile cards truncover;
length empid $6 transactiontype $10;
input;
empid = scan(_infile_,1);
do i = 2 to countw(_infile_);
  transactiontype = scan(_infile_,i);
  select (put(transactiontype,$transacts.));
    when ('A1') output a1;
    when ('A2') output a2;
    otherwise;
  end;
end;
drop i;
cards; 
123456 Transact1 Transact2 Transact3
456789 Transact1 Transact2
;
run;

You might want to remove the otherwise to get an ERROR if you catch an unexpected transaction type.

[ChrisNZ]

This works:

data HAVE;
  informat TRANSACTIONTYPE $10.;
  infile cards missover truncover;
  input EMPLOYEEID @ ;
  do until (TRANSACTIONTYPE=' '); 
    input TRANSACTIONTYPE $ @;
    if TRANSACTIONTYPE ne ' ' then output;
  end;
cards;
123456 Transact1 Transact2 Transact3
456789 Transact1 Transact2
run;

proc sort data=HAVE; 
  by TRANSACTIONTYPE;
run;

data DETAILS;
  informat TRANSACTIONTYPE $10.;
  input TRANSACTIONTYPE $ SERVICE $;
cards;
Transact1 A1        
Transact2 A2
Transact3 A1
run;

data FILE1
     FILE2;
  merge HAVE DETAILS;
  by TRANSACTIONTYPE;
  if SERVICE='A1' then output FILE1;
  if SERVICE='A2' then output FILE2;
run;

A few assumptions about the data are made here.

Please understand the steps, as described at @Reeza, to correct and fit to your exact needs.