topic Hash or Array solution ? in SAS Programming

Hash or Array solution ?

AshokD — Thu, 23 Aug 2018 03:01:13 GMT

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 .

Re: Hash or Array solution ?

Reeza — Thu, 23 Aug 2018 03:40:29 GMT

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 .

Re: Hash or Array solution ?

ChrisNZ — Thu, 23 Aug 2018 04:59:38 GMT

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.

Re: Hash or Array solution ?

AshokD — Thu, 23 Aug 2018 05:53:20 GMT

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.

Re: Hash or Array solution ?

ChrisNZ — Thu, 23 Aug 2018 06:45:34 GMT

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.

Re: Hash or Array solution ?

Kurt_Bremser — Thu, 23 Aug 2018 07:53:45 GMT

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.

Re: Hash or Array solution ?

AshokD — Thu, 23 Aug 2018 11:33:32 GMT

@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.

Re: Hash or Array solution ?

AshokD — Mon, 27 Aug 2018 00:14:33 GMT

@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.