Help using Base SAS procedures

"fill out" or expand a dataset

Accepted Solution Solved
Reply
N/A
Posts: 1
Accepted Solution

"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

Re: "fill out" or expand a dataset


All Replies
Trusted Advisor
Posts: 1,228

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: 340

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: 308

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

1code.JPG

Output

2output.JPG

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 283 views
  • 6 likes
  • 6 in conversation