SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
vtan9999
Fluorite | Level 6

Hi ,I have the following sample and am looking for help.  Thanks in advance.

ID  Ticket   Date1  Data2  Code1 Code2 Code3 Code4 Cod5

1    clm1     Jan      Feb                  V01     V02      E25  

1    clm2     Feb      Mar        D01                            C02

1    clm3     Mar      Apr                                            B01    V02

2    clm1      Jan       Mar                                                     B01

3    clm1      Jan     May                     B01    C01                D01

3    clm2       Feb     Jun                                V01    E02

Result Group ID:

Data want;
 set have;    run;   

ID  Ticket   Date1  Data2  1stCode  2ndCode     Ticket   Date1  Data2  1stCode  2ndCode    Ticket   Date1  Data2  1stCode 2ndCode1 

1     clm1     Jan     Feb      V01          V02             Clm2    Feb      Mar     D01        C02            clm3     Mar     Apr       B01         V02

2     clm2      Jan     Mar      B01

3     clm1       Jan    May      B01         C01             clm2     Feb       Jun    V01         E02

4 REPLIES 4
Ksharp
Super User
data have;
infile cards truncover;
input ID  (Ticket   Date1  Date2  Code1 Code2 Code3 Code4 Code5) ($);
cards;
1    clm1     Jan      Feb          .        V01     V02      E25  
1    clm2     Feb      Mar        D01       . .                     C02
1    clm3     Mar      Apr           . . .                      B01    V02
2    clm1      Jan       Mar       . . .  .                      B01
3    clm1      Jan     May          .           B01    C01       .         D01
3    clm2       Feb     Jun       .  .                       V01    E02 .
;
run;
data temp;
 set have;
 array x{*} $ code:;
 x1=scan(catx('|',of x{*}),1,'|');
 x2=scan(catx('|',of x{*}),2,'|');
run;
proc sql noprint;
select max(n) into : n
 from (select count(*) as n from have group by id);
quit;
proc summary data=temp;
by id ;
output out=want idgroup(out[&n] (ticket date1 date2 x1 x2)=);
run;
proc print noobs;run;

vtan9999
Fluorite | Level 6

Thank you,  . The program below works as what I expect. 

data temp;
set have;
array x{*} $ code:;
x1=scan(catx('|',of x{*}),1,'|');
x2=scan(catx('|',of x{*}),2,'|');
run;

However, the last program run into problem.  The proc summary run into problem.

proc summary data=temp;
by id ;
output out=want idgroup(out[&n] (ticket date1 date2 x1 x2)=);
run;
proc print noobs;run;

  Here is what it displays:

"The parameter value 17641 is not within the required range of 1 and 100".   I work around by creating 3 data steps and merge them back at the end.  It works fine but I am looking for a better way to do.  Thanks for your input.

Ksharp
Super User
I can't believe you need so many variables.
Check MERGE Skill proposed by me,Matt,Arthur.T
http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf


data have;
infile cards truncover;
input ID  (Ticket   Date1  Date2  Code1 Code2 Code3 Code4 Code5) ($);
cards;
1    clm1     Jan      Feb          .        V01     V02      E25  
1    clm2     Feb      Mar        D01       . .                     C02
1    clm3     Mar      Apr           . . .                      B01    V02
2    clm1      Jan       Mar       . . .  .                      B01
3    clm1      Jan     May          .           B01    C01       .         D01
3    clm2       Feb     Jun       .  .                       V01    E02 .
;
run;
data temp;
 set have;
 by id;
 if first.id then n=0;
 n+1;
 array x{*} $ code:;
 x1=scan(catx('|',of x{*}),1,'|');
 x2=scan(catx('|',of x{*}),2,'|');
 keep id ticket date1 date2 x1 x2 n;
run;
proc freq data=temp noprint;
table n/out=key nopercent;
run;
data _null_;
 set key end=last;
 if _n_=1 then call execute('data want;merge ');
 call execute(catt('temp(where=(n=',n,') rename=(ticket=ticket_',n,' 
 date1=date1_',n,' date2=date2_',n,' x1=x1_',n,' x2=x2_',n,'))'));
 if last then call execute(';by id;drop n;run;');
run;

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1010 views
  • 2 likes
  • 3 in conversation