BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
molla
Fluorite | Level 6

i HAVE THE FOLLOWING DATASET:

 

DATA dem;
INPUT SITEID $ COUNTRY $ INVNAM $ subid ;
CARDS;
001 USA SRINU 101
001 USA RUBY 102
001 USA SAS 103
001 USA MEHAR 111
001 USA QLAS 104
002 USA SURI 105
002 USA KEERTI 106
002 USA DEEP 107
003 USA SATYA 108
004 USA XYZ 109
;
RUN;

I need output as

001 USA SRINU,RUBY,SAS,MEHAR,QLAS 101
001 USA SRINU,RUBY,SAS,MEHAR,QLAS 102
001 USA SRINU,RUBY,SAS,MEHAR,QLAS 103
001 USA SRINU,RUBY,SAS,MEHAR,QLAS 111
001 USA SRINU,RUBY,SAS,MEHAR,QLAS 104
002 USA SURI,KEERTI,DEEP 105
002 USA SURI,KEERTI,DEEP 106
002 USA SURI,KEERTI,DEEP 107
003 USA SATYA 108
004 USA XYZ 109

 

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

Here is one way to obtain the result you want:

 

data want (keep=siteid country sites subid);
  length sites $100.;
  do until (last.siteid);
    set dem;
    by siteid;
    if first.siteid then call missing(sites);
    sites=catx(',',sites,invnam);
  end;
   do until (last.siteid);
    set dem;
    by siteid;
    output;
  end;
run;

Art, CEO, AnalystFinder.com

 

View solution in original post

3 REPLIES 3
art297
Opal | Level 21

Here is one way to obtain the result you want:

 

data want (keep=siteid country sites subid);
  length sites $100.;
  do until (last.siteid);
    set dem;
    by siteid;
    if first.siteid then call missing(sites);
    sites=catx(',',sites,invnam);
  end;
   do until (last.siteid);
    set dem;
    by siteid;
    output;
  end;
run;

Art, CEO, AnalystFinder.com

 

molla
Fluorite | Level 6

Hi I got the result,cud u pls explain me the program

art297
Opal | Level 21

Sure. The first part of the programs reach each record. For each siteid it builds a variable called sites that contains all of the invnams that the particular siteid has records for. By the time it reaches the last siteid, sites contains all of the invnams for that siteid.

 

data want (keep=siteid country sites subid);
  length sites $100.;
  do until (last.siteid);
    set dem;
    by siteid;
    if first.siteid then call missing(sites);
    sites=catx(',',sites,invnam);
  end;

As soon as the last record for a siteid is processed, the second part of the program takes over and simply outputs the results.

   do until (last.siteid);
    set dem;
    by siteid;
    output;
  end;
run;

You can Google the method to understand more about it. It's referred to as a Double DOW.

 

Art, CEO, AnalystFinder.com

 

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
  • 3 replies
  • 792 views
  • 1 like
  • 2 in conversation