topic Record replication in SAS Procedures

Record replication

venkaraj — Fri, 13 Jun 2014 08:46:50 GMT

Hi,

I need a help in my current requirement. Requirement is as below

I have one sas dataset with only account number as below

12345

23456

34567

45678

I have another SAS dataset which has a dummy account number and 98 bytes of information as below

565651xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

565652xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

565653xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

565654xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

I need a new SAS dataset which should be like this

123451xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

123452xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

123453xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

123454xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

234561xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

234562xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

234563xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

234564xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

345671xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

345672xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

345673xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

345674xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

456781xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

456782xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

456783xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

456784xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

When I try coding a dataset with 2 set statements, then I a getting only one set of 4 records and not getting the remaining. My dataset is as below

DATA NEW;

DO UNTIL(EOF1);

SET DATA1; /* This is the first dataset having 4 unique account number */

DO UNTIL(EOF2); /* This is the second dataset having dummy account number and 4 records */

SET DATA2;

FILE OUTDATA; /* This is where the output has to go */

PUT @001 ACCT_NO 5.

@006 DESCRIPT $93;

END;

Can anybody help me how I can get the information from second dataset to be repeated for number of records in first dataset. I men, if First dataset has 5 records and second dataset has 4 records with dummy account number, then my output should have 5 * 4 20 records

Thanks in advance

Venkat

Re: Record replication

stat_sas — Fri, 13 Jun 2014 10:14:44 GMT

Try this.

data data1;
input acct_no;
datalines;
12345
23456
34567
45678
;

data data2;
input dm_acct_no $ 65.;
datalines;
565651xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
565652xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
565653xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
565654xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
;

proc sql;
create table final as
select acct_no,dm_acct_no from data1,data2;
quit;

Re: Record replication

venkaraj — Fri, 13 Jun 2014 10:56:00 GMT

Thanks stat@sas.

This solution worked as expected for me.

Venkatesan

Re: Record replication

naveen20jan — Fri, 13 Jun 2014 11:40:44 GMT

Hi All,

I have a bit short and simple approach to solve the problem ;

data data1;
input acct_no;
datalines;
12345
23456
34567
45678
;

data data2;
input dm_acct_no $ 65.;
datalines;
565651xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
565652xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
565653xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
565654xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
;

proc transpose data = data1 out = d2 ;

run;

data d3 (keep = desired_var);

if _N_ = 1 then do ;

set d2 ;

array s (4) col1 - col4 ;

end;

set data2 ;

do i = 1 to 4 ;

z = substr (dm_acct_no ,6) ;

desired_var = cats(s(i),z) ;

output ;

end;

run;

proc sort data = d3 ; by desired_var ; run;

This will give us the desired result and we can chage the code easily to accomadate more varibales

;

Re: Record replication

Ksharp — Fri, 13 Jun 2014 12:24:56 GMT

 
data data1;
input acct_no $;
datalines;
12345
23456
34567
45678
;

data data2;
input dm_acct_no $ 65.;
datalines;
565651xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
565652xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
565653xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
565654xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
;
proc sql;
create table final as
select strip(acct_no)||substr(dm_acct_no,6) as no from data1,data2;
quit;

Xia Keshan