- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;