DATA Step, Macro, Functions and more

BASE SAS

Accepted Solution Solved
Reply
Contributor
Posts: 66
Accepted Solution

BASE SAS

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

 


Accepted Solutions
Solution
‎03-14-2017 02:40 PM
PROC Star
Posts: 7,492

Re: BASE SAS

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


All Replies
Solution
‎03-14-2017 02:40 PM
PROC Star
Posts: 7,492

Re: BASE SAS

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

 

Contributor
Posts: 66

Re: BASE SAS

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

PROC Star
Posts: 7,492

Re: BASE SAS

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

 

☑ This topic is solved.

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

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