I need to separate all m1 and m2 values that belong to PL values. I have a spreasheet that has m1,m2,m3... measure which denotes
the PL values. I need to create the separate variables for each 'm' measure. Please let me know know if the following example makes any sense to you.
Input table:
ME ZP YR PL
m1 7501 2001 85
m1 7502 2001 22
m1 7503 2001 45
m1 7504 2001 85
m1 7505 2001 11
m1 7505 2001 65
m2 7501 2001 70
m2 7502 2001 55
m2 7503 2001 09
m2 7504 2001 19
m2 7505 2001 33
m2 7505 2001 72
Need the following output:
ZP YR m1 m2
7501 2001 85 70
7502 2001 22 55
7503 2001 45 09
7504 2001 85 19
7505 2001 11 19
7505 2001 65 33
7501 2001 70 72
How about using skill of merging by itself:
data have; input ME $ ZP YR PL; cards; m1 7501 2001 85 m1 7502 2001 22 m1 7503 2001 45 m1 7504 2001 85 m1 7505 2001 11 m1 7505 2001 65 m2 7501 2001 70 m2 7502 2001 55 m2 7503 2001 09 m2 7504 2001 19 m2 7505 2001 33 m2 7505 2001 72 ; run; proc sql noprint; select distinct cats('have(where=(me="',me,'") rename=(pl=',me,'))') into : list separated by ' ' from have; quit; data want(drop=me); merge &list; by zp yr; run;
Ksharp
Presuming that there was a typo in the 5th record of your need file, I think that the following will produce what you want:
data need1 need2;
set have;
if me='m1' then output need1;
else output need2;
run;
data want;
set need1 (drop=me rename=(pl=m1));
set need2 (keep=pl rename=(pl=m2));
run;
Hi,
How did you come up with 7 observations in your output?
I only have 6 obs.
data have;
input ME $ ZP YR PL;
cards;
m1 7501 2001 85
m1 7502 2001 22
m1 7503 2001 45
m1 7504 2001 85
m1 7505 2001 11
m1 7505 2001 65
m2 7501 2001 70
m2 7502 2001 55
m2 7503 2001 09
m2 7504 2001 19
m2 7505 2001 33
m2 7505 2001 72
;
data m1(rename=(pl=m1)) m2(rename=(pl=m2)) other;
set have;
select (me);
when ('m1') output m1;
when ('m2') output m2;
otherwise output other;
end;
data want(drop=me) ;
merge m1 m2;
by zp yr;
run;
proc print;run;
Obs ZP YR m1 m2
1 7501 2001 85 70
2 7502 2001 22 55
3 7503 2001 45 9
4 7504 2001 85 19
5 7505 2001 11 33
6 7505 2001 65 72
Linlin
Here is another more general approaches in an attempt to eliminate the hard coding:
data have;
infile cards;
input ME $ ZP YR PL;
cards;
m1 7501 2001 85
m1 7502 2001 22
m1 7503 2001 45
m1 7504 2001 85
m1 7505 2001 11
m1 7505 2001 65
m2 7501 2001 70
m2 7502 2001 55
m2 7503 2001 09
m2 7504 2001 19
m2 7505 2001 33
m2 7505 2001 72
m3 7501 2001 70
m3 7502 2001 55
m3 7503 2001 09
m3 7504 2001 19
m3 7505 2001 33
m3 7505 2001 72
;
;
/*This to hash out multiple files base on ME*/
data _null_;
length n_me $100;
retain n_me;
declare hash _out(ordered: 'a');
_out.definekey ('_n_');
_out.definedata ('me','zp', 'yr', 'pl');
_out.definedone();
do _n_=1 by 1 until (last.me);
set have;
by me;
_out.add();
end;
n+1;
n_me=catx(' ',n_me,me);
_out.output(dataset : 'out'||me);
call symput('n',n);
call symput('n_me',n_me);
run;
/*This to stich them together*/
%macro merg;
%do i=1 %to &n;
%if &i=1 %then %do;
data want;
set out%scan(&n_me,&i) (drop=me rename=pl=%scan(&n_me,&i));
run;
%end;
%else %do;
data want;
merge want out%scan(&n_me,&i) (drop=me rename=pl=%scan(&n_me,&i));
by yr zp;
run;
%end;
%end;
%mend;
%merg
proc print;run;
Regards,
Haikuo
Here is a similar version:
data have;
infile cards;
input ME $ ZP YR PL;
cards;
m1 7501 2001 85
m1 7502 2001 22
m1 7503 2001 45
m1 7504 2001 85
m1 7505 2001 11
m1 7505 2001 65
m2 7501 2001 70
m2 7502 2001 55
m2 7503 2001 09
m2 7504 2001 19
m2 7505 2001 33
m2 7505 2001 72
m3 7501 2001 70
m3 7502 2001 55
m3 7503 2001 09
m3 7504 2001 19
m3 7505 2001 33
m3 7505 2001 72
;
proc sql noprint ;
select distinct me into :mes separated by ' 'from have;
quit;
data _null_;
declare hash lin(ordered:'a');
lin.definekey('_n_');
lin.definedata('me','zp', 'yr', 'pl');
lin.definedone();
do _n_=1 by 1 until (last.me);
set have;
by me;
lin.add();
end;
lin.output(dataset:me);
run;
%macro test();
data want(drop=me);
merge
%do i=1 %to %sysfunc(countw(&mes));
%scan(&mes,&i)(rename=(pl=%scan(&mes,&i)))
%end;;
by zp yr;
run;
%mend;
%test()
proc print;run;
Linlin: It's been a week and the OP has never given anyone any indicating of whether any of the responses were on the right track. I think everyone's time would be better off responding to unanswered questions.
Normally I would just use PROC TRANSPOSE with by variables of ZP and YR and transpose the value of PL using the value of ME as the variable name. But you have duplicate rows for the case when ZP=7505 and YR=2001. When you have duplicate values for your grouping variables how do you want to match the values of the new M1 and M2 variables?
One way is to just keep the order that they had in the original data and match the first M1 with the first M2.
data have;
input ME $ ZP YR PL;
cards;
m1 7501 2001 85
m1 7502 2001 22
m1 7503 2001 45
m1 7504 2001 85
m1 7505 2001 11
m1 7505 2001 65
m2 7501 2001 70
m2 7502 2001 55
m2 7503 2001 09
m2 7504 2001 19
m2 7505 2001 33
m2 7505 2001 72
run;
data have ;
set have ;
by me zp yr ;
if first.yr then order=1; else order+1;
run;
proc sort data=have;
by zp yr order me ;
run;
proc transpose let data=have out=want (drop=_:);
by zp yr order;
var pl;
id me;
run;
How about using skill of merging by itself:
data have; input ME $ ZP YR PL; cards; m1 7501 2001 85 m1 7502 2001 22 m1 7503 2001 45 m1 7504 2001 85 m1 7505 2001 11 m1 7505 2001 65 m2 7501 2001 70 m2 7502 2001 55 m2 7503 2001 09 m2 7504 2001 19 m2 7505 2001 33 m2 7505 2001 72 ; run; proc sql noprint; select distinct cats('have(where=(me="',me,'") rename=(pl=',me,'))') into : list separated by ' ' from have; quit; data want(drop=me); merge &list; by zp yr; run;
Ksharp
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.