Solved
Not applicable
Posts: 1

# "fill out" or expand a dataset

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

1

2

3

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

Accepted Solutions
Solution
‎11-03-2014 05:26 AM
Frequent Contributor
Posts: 81

All Replies
Posts: 1,270

## Re: "fill out" or expand a dataset

proc sql;

create table want as

select letter,set2.num

from set1,set2;

quit;

Contributor
Posts: 27

## Re: "fill out" or expand a dataset

proc sql;

create table ab as

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

from a,b;

select * from ab;

quit;

Super Contributor
Posts: 355

## Re: "fill out" or expand a dataset

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;

Super Contributor
Posts: 319

## Re: "fill out" or expand a dataset

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;

Solution
‎11-03-2014 05:26 AM
Frequent Contributor
Posts: 81

## Re: "fill out" or expand a dataset

Output

🔒 This topic is solved and locked.