How would I divide 1 variable to 2 or more ?

[Roger]

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

[art297]

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;

[Linlin]

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

[Haikuo]

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

[Linlin]

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;

[art297]

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.

[Tom]

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;

[Ksharp · Accepted solution]

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