topic Re: Manipulating Proc Transpose in SAS Programming

Manipulating Proc Transpose

Errant — Sat, 21 Jul 2018 20:26:09 GMT

Hello,

I have a dataset that looks like this. There are indicator variables for the purchase of each brand, for each brand display and each brand feature. There's also the price for each brand in each observation.

The data set that I want looks like. Each subject will be split into 4 observations, one for each brand.

obs	OBS	Purchase	Brand	Feature	Display	Price
1	1	0	Private	0	0	0.709999979
2	1	0	Keebler	0	0	0.980000019
3	1	1	Nabisco	0	0	0.879999995
4	1	0	Sunshine	0	0	1.199999928

Essentially it's a wide to long transformation and I believe I need to merge the certain variables. So far I've been trying to use proc transpose, in several different ways. One example being :

proc transpose data=HW5.crackers_hw5;
   var DisplKeebler  DisplNabisco DisplPrivate DisplSunshine FeatKeebler FeatNabisco FeatPrivate FeatSunshine; 
   by obs KEEBLER NABISCO PRIVATE SUNSHINE;
run;

My first question is, how do I merge the indicator variables appropriately? Secondly, what would be the best way to manipulate proc transpose to get this data set?

Thank you

Re: Manipulating Proc Transpose

art297 — Sat, 21 Jul 2018 21:01:19 GMT

I think you will be better off accomplishing the task in a datastep using arrays. e.g.:

data want (keep=obs Purchase Brand Feature Display Price);
  set have;
  array pur(*) PRIVATE SUNSHINE KEEBLER NABISCO;
  array pri(*) PRICEPRIVATE PRICESUNSHINE PRICEKEEBLER PRICENABISCO;
  array fea(*) FeaturePrivate FeatureSunshine FeatureKeebler FeatureNabisco;
  array dis(*) DisplPrivate DisplSunshine DisplKeebler DisplNabisco;
  do i=1 to 4;
    Purchase=pur(i);
    Brand=vname(pur(i));
    Feature=fea(i);
    Display=dis(i);
    Price=pri(i);
    output;
  end;
run;

Art, CEO, AnalystFinder.com

Re: Manipulating Proc Transpose

Errant — Sat, 21 Jul 2018 21:22:12 GMT

Thank you, it looks great so far! However, for some reason, it codes feature as missing variables entirely. Do you know how I can prevent this?

Re: Manipulating Proc Transpose

PaigeMiller — Sat, 21 Jul 2018 21:26:45 GMT

Errant, it would sure help if you showed us the code you used.

Re: Manipulating Proc Transpose

Errant — Sat, 21 Jul 2018 21:34:51 GMT

Sorry about that. Essentially, just the same code posted above.

data want new (keep=obs Purchase Brand Feature Display Price);
  set crackers;;
  array pur(*) PRIVATE SUNSHINE KEEBLER NABISCO;
  array pri(*) PRICEPRIVATE PRICESUNSHINE PRICEKEEBLER PRICENABISCO;
  array fea(*) FeaturePrivate FeatureSunshine FeatureKeebler FeatureNabisco;
  array dis(*) DisplPrivate DisplSunshine DisplKeebler DisplNabisco;
  do i=1 to 4;
    Purchase=pur(i);
    Brand=vname(pur(i));
    Feature=fea(i);
    Display=dis(i);
    Price=pri(i);
    output;
  end;
run;

Re: Manipulating Proc Transpose

PaigeMiller — Sat, 21 Jul 2018 21:35:34 GMT

So the variables names in the code do not match the variable names in your data set for features, this is easily fixed.

Re: Manipulating Proc Transpose

art297 — Sat, 21 Jul 2018 21:38:42 GMT

I had the wrong variable names for feature. Try:

data want (keep=obs Purchase Brand Feature Display Price);
  set have;
  array pur(*) PRIVATE SUNSHINE KEEBLER NABISCO;
  array pri(*) PRICEPRIVATE PRICESUNSHINE PRICEKEEBLER PRICENABISCO;
  array fea(*) FeatPrivate FeatSunshine FeatKeebler FeatNabisco;
  array dis(*) DisplPrivate DisplSunshine DisplKeebler DisplNabisco;
  do i=1 to 4;
    Purchase=pur(i);
    Brand=vname(pur(i));
    Feature=fea(i);
    Display=dis(i);
    Price=pri(i);
    output;
  end;
run;

Art, CEO, AnalystFinder.com

Re: Manipulating Proc Transpose

Errant — Sat, 21 Jul 2018 21:55:26 GMT

Thank you so much, incredibly helpful.