BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Errant
Obsidian | Level 7

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

7 REPLIES 7
art297
Opal | Level 21

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
Obsidian | Level 7

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

 

PaigeMiller
Diamond | Level 26

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

--
Paige Miller
Errant
Obsidian | Level 7

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
Diamond | Level 26

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
art297
Opal | Level 21

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
Obsidian | Level 7
Thank you so much, incredibly helpful.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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