topic Re: How do I create many variables from an existing variable? in SAS Programming

How do I create many variables from an existing variable?

JDDowell — Thu, 12 Dec 2019 17:00:33 GMT

Hello all, I have a data set of individuals and when they leave a location. I am trying to find the flow of people by month. My data set is like the code below but instead I have some 11,500 entries and exits over the course of 120 months for 3 types of locations.

Data Have;
input id $ location $6. date MONYY6.;
datalines;

1 Type_A Nov05
2 Type_B Dec05
3 Type_C Jan06
4 Type_B Nov05
5 Type_A Nov05
6 Type_B Jan06
;

So. I would like to create a variable for each month so that I can sum the flow from each location type. The data set would look something like the following.

data Want;
input id $ location $6. date MONYY6. Nov05 $ Dec05 $ Jan06 $;
datalines;

1 Type_A Nov05 1 0 0
2 Type_B Dec06 0 1 0
3 Type_C Jan01 0 0 1
4 Type_B Nov05 1 0 0
5 Type_A Nov05 1 0 0
6 Type_B Jan01 0 0 1
;

Thank you

Re: How do I create many variables from an existing variable?

novinosrin — Thu, 12 Dec 2019 17:36:28 GMT



Data Have;
input id $ location $6. date MONYY6.;
format date monyy6.;
datalines;
1 Type_A Nov05
2 Type_B Dec05
3 Type_C Jan06
4 Type_B Nov05
5 Type_A Nov05
6 Type_B Jan06
;
proc sql noprint;
select put( date,monyy6.) into :list separated by ' ' 
from (select distinct date from have);
quit;
%put &=list &=sqlobs;

data want;
set have;
array t(*) &list;
array tt(9999)$32 _temporary_;
retain tt;
if _n_=1 then do;
 do _n_=1 to dim(t);
  tt(_n_)=vname(t(_n_));
 end;
end;
 do _n_=1 to dim(t);
  t(_n_)=0;
 end;
k=whichc(put(date,monyy6. -l),of tt(*));
if k then t(k)=1;
drop k;
run;

Re: How do I create many variables from an existing variable?

PaigeMiller — Thu, 12 Dec 2019 17:40:30 GMT

@JDDowell wrote:

So. I would like to create a variable for each month so that I can sum the flow from each location type. The data set would look something like the following.

Sums by some sort of group do not require these 0/1 variables. It can be done very easily in PROC SUMMARY.

Please show us the real input and final desired output rather than this unnecessary intermediate step of creating zero/one variables.

Re: How do I create many variables from an existing variable?

ed_sas_member — Thu, 12 Dec 2019 19:00:48 GMT

Hi @JDDowell

There is no need to create dummy variables to do that.

You can try the following code:

proc freq data=have;
	tables date*location / out=want nocum nopercent norow nocol;
	format date MONYY6.;
run;

Re: How do I create many variables from an existing variable?

JDDowell — Fri, 13 Dec 2019 16:17:35 GMT

I can't share real input because of the information contained within it, could you be more specific as to what you are wanting? The only real difference would be that my dates have days, they're not really all lumped into a month, I've only formatted them that way. I think for that reason that if I did a proc summary by date, they would sum for each individual date.