Record replication

[venkaraj]

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;

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

[stat_sas]

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;

[venkaraj]

Thanks stat@sas.

This solution worked as expected for me.

Venkatesan

[naveen20jan]

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

;

[Ksharp]

 
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