topic Re: "fill out" or expand a dataset in SAS Procedures

"fill out" or expand a dataset

jcsas — Sun, 02 Nov 2014 20:40:24 GMT

Hi,

Does anyone know a way to "fill out" a dataset with values from a second dataset using merge or set or other simple statements? For example, I might have one dataset as:

letter num

a .

b .

c .

And a second datset:

num

I need the resulting dataset to look like:

letter num

a 1

a 2

a 3

b 1

b 2

b 3

c 1

c 2

c 3

I really don't want to use an array. Thanks for any ideas!

Jon

Re: "fill out" or expand a dataset

stat_sas — Sun, 02 Nov 2014 21:34:58 GMT

proc sql;

create table want as

select letter,set2.num

from set1,set2;

quit;

Re: "fill out" or expand a dataset

bharathtuppad — Mon, 03 Nov 2014 02:37:55 GMT

proc sql;

create table ab as

select letter, coalesce(a.num, b.num)

from a,b;

select * from ab;

quit;

Re: "fill out" or expand a dataset

user24feb — Mon, 03 Nov 2014 07:43:18 GMT

I really think the sql-version is an option here. But this might work as well, if the number of observations to fill out is fixed:

Data A;
Input Letter $ @@;
Call Missing (Num);
Datalines;
a b c
;
Run;

Data B;
Input Num @@;
Datalines;
1 2 3
;
Run;

Data Want;
Set A;
By Letter;
If First.Letter Then Do;
    i=0;
Set B Nobs=MaxNum;
    Do Until (i=MaxNum);
   i=i+1;
      Set B Point=i;
   Output;
    End;
End;
Run;

Re: "fill out" or expand a dataset

Loko — Mon, 03 Nov 2014 10:18:14 GMT

Hello,

data master;
input letter $ num;
datalines;
a .
b .
c .
x .
y .
z .
;

data trans;
input num;
datalines;
1
2
3
;

data want;
do until(ltrans);
i+1;
set trans end=ltrans;
call symput('var'||put(i,1.), put(num, 1.));

end;

do until(lmaster);
j+1;
set master end=lmaster;
if mod(j,3)=1 then num=symget('var1');
else if mod(j,3)=2 then num=symget('var2');
else if mod(j,3)=0 then num=symget('var3');
output;
end;

stop;
drop i j;
run;

Re: "fill out" or expand a dataset

damanaulakh88 — Mon, 03 Nov 2014 10:26:51 GMT

Output