topic Re: Need Help !!! in SAS Programming

Need Help !!!

vtan9999 — Wed, 25 Apr 2018 02:50:36 GMT

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

Re: Need Help !!!

Patrick — Wed, 25 Apr 2018 03:27:20 GMT

https://communities.sas.com/t5/SAS-Support-Communities/How-to-ask-a-good-question-and-receive-a-fast-reply/ta-p/417192

Re: Need Help !!!

Ksharp — Wed, 25 Apr 2018 13:43:01 GMT

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;

Re: Need Help !!!

vtan9999 — Wed, 25 Apr 2018 18:29:00 GMT

Thank you, Ksharp . 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.

Re: Need Help !!!

Ksharp — Thu, 26 Apr 2018 12:33:02 GMT

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;