Manipulating Proc Transpose

[Errant]

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

[art297]

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

[Errant]

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?

[PaigeMiller]

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

[Errant]

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;

[PaigeMiller]

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

[art297 · Accepted solution]

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

[Errant]

Thank you so much, incredibly helpful.