Manipulating Proc Transpose

Accepted Solution Solved
Reply
Contributor
Posts: 27
Accepted Solution

Manipulating Proc Transpose

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. cracker.jpg

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

obsOBSPurchaseBrandFeatureDisplayPrice
110Private 000.709999979
210Keebler000.980000019
311Nabisco000.879999995
410Sunshine001.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

 


Accepted Solutions
Solution
a month ago
Super User
Posts: 8,220

Re: Manipulating Proc Transpose

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

 

View solution in original post


All Replies
Super User
Posts: 8,220

Re: Manipulating Proc Transpose

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

 

Contributor
Posts: 27

Re: Manipulating Proc Transpose

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?

cracker1.jpg

 

Respected Advisor
Posts: 3,288

Re: Manipulating Proc Transpose

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

--
Paige Miller
Contributor
Posts: 27

Re: Manipulating Proc Transpose

Posted in reply to PaigeMiller

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;
Respected Advisor
Posts: 3,288

Re: Manipulating Proc Transpose

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

--
Paige Miller
Solution
a month ago
Super User
Posts: 8,220

Re: Manipulating Proc Transpose

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

 

Contributor
Posts: 27

Re: Manipulating Proc Transpose

Thank you so much, incredibly helpful.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 226 views
  • 5 likes
  • 3 in conversation