BASE SAS

[molla]

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

[art297 · Accepted solution]

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]

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

[art297]

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