topic Re: How would I divide 1 variable to 2 or more ? in SAS Programming

How would I divide 1 variable to 2 or more ?

Roger — Wed, 08 Feb 2012 18:33:40 GMT

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 would I divide 1 variable to 2 or more ?

art297 — Wed, 08 Feb 2012 18:48:48 GMT

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;

Re: How would I divide 1 variable to 2 or more ?

Linlin — Wed, 08 Feb 2012 18:59:02 GMT

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

How would I divide 1 variable to 2 or more ?

Haikuo — Wed, 08 Feb 2012 20:18:57 GMT

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;

%mend;

%merg

proc print;run;

Regards,

Haikuo

How would I divide 1 variable to 2 or more ?

Tom — Wed, 08 Feb 2012 21:45:51 GMT

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;

Re: How would I divide 1 variable to 2 or more ?

Linlin — Sun, 12 Feb 2012 02:03:56 GMT

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;

Re: How would I divide 1 variable to 2 or more ?

art297 — Sun, 12 Feb 2012 03:22:16 GMT

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.

Re: How would I divide 1 variable to 2 or more ?

Ksharp — Mon, 13 Feb 2012 07:37:01 GMT

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