BookmarkSubscribeRSS Feed
pawandh
Fluorite | Level 6

I have two datasets "A" and "B".

data a;

name age frnds;

raj 12 a

raj 12 b

raj 12 c

rahul 21 a

rahul 21 d

rahul 21 e

rahul 21 f

raj 21 f

raj 21 h...... .... .....

;

like this there are many observations in my data set A.And data set B like

data B;

name age frnds;

raj     12       4

rahul 21   3

raj     21    5

;

so from data set "A" I want that much observations as there are numbers in dataset b in "frnds".For ex in data set b for "raj" "12"

there are "4" in frnds so I need first 4 observations from data set A .Similarly for rahul need first 3 observations from data set A

and 5 observations for "raj" "21" from data set A and so on.

 

Final op

name age frnds

raj 12       a

raj 12       b

raj 12       c

raj 12       d  

raj 12       e 

rahul 21   a

rahul 21   f

rahul 21   g

 

and so on.......

2 REPLIES 2
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Well, I would say that this seems to be a pointless endeavour, you have the data you have, why try to create data which isn't there.  Anyway, it can be done:

data datab (drop=frnds);
  set datab;
  do i=1 to frnds;
    output;
  end;
run;

proc sql;
  create table WANT as
  select coalesce(A.NAME,B.NAME) as NAME,
         coalesce(A.AGE,B.AGE) as AGE,
         A.FRNDS
  from   DATAA A
  full join DATAB B
  on     A.NAME=B.NAME 
  and    A.AGE=B.AGE;
quit;
  
    

What this does is expand datab to have as many rows as stipulated in frnds variable.  This is then joined onto dataa, using an all to all join, to expand dataa to have the same number of records as datab.

Ksharp
Super User

data a;
input name $ age frnds $;
cards;
raaj 12 a
raaj 12 b
raaj 12 c
rahul 21 a
rahul 21 d
rahul 21 e
rahul 21 f
raj 21 f
raj 21 h
;
run;
data B;
input name $ age count;
cards;
raaj     12       4
rahul 21   3
raj     21    5
;
run;
data want;
 merge a b;
 by name age;
 if first.age then n=0;
 n+1;
 if n le count;
 drop n count;
run;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 475 views
  • 1 like
  • 3 in conversation