DATA Step, Macro, Functions and more

How to Transform Data Set from Long to Wide Across Multiple Variables

Reply
Occasional Contributor
Posts: 9

How to Transform Data Set from Long to Wide Across Multiple Variables

Hello!

Please help me transpose this data set from Long to Wide.

Here is what I have:

DayofWeekUserWebsiteMeanSumFreq
Saturday17306100MiKandi10101
Friday17306100Twitter75751
Sunday17306100Voice Search151.674553
Saturday17306100icomfort30301
Saturday17306243AOL872613
Sunday17306243Email120.65796366
Monday17306243Firefox5365361
Tuesday17306243Google15151
Wednesday17306243IMDb202.54052
Thursday17306243Landry's, Inc25251
Thursday18114509Norton Mobile45451
Thursday18114509Notes317.5317510
Thursday18114509Our Groceries510.94817516
Friday18072613Chase245245010
Friday18072613Check374.3829958
Friday18072613Craigslist55551
Wednesday18062931Barcode Scanner551
Wednesday18062931BofA69.92390913
Wednesday18062931Facebook343.9435426103

Here is what I need:

Essentially multiple each day of the week (7 days of the week) by each of the 3 variables (Mean, Sum, Freq) for a total of 21 new variables. I want to keep the User and Website columns constant and unchanged.

Could someone please help me attack this with a do loop and array, so that I am able to replicate it using the same procedure but for a WeekoftheYear variable (which holds 52 different values, one for each week of the year). Thank you!

UserWebsiteMeanSundayMeanMondayMeanTuesdayMeanWednesdayMeanThursdayMeanFridayMeanSaturdayFreqSundayFreqMondayFreqTuesdayFreqWednesdayFreqThursdayFreqFridayFreqSaturdayFreqSundaySumMondaySumTuesdaySumWednesdaySumThursdaySumFridaySumSaturday
17306100icomfort
17306100MiKandi
17306100Twitter
17306100Voice Search
17306243AOL
17306243Email
17306243Firefox
17306243Google
17306243IMDb
17306243Landry's, Inc
18062931Barcode Scanner
18062931BofA
18062931Facebook
18072613Chase
18072613Check
18072613Craigslist
18114509Norton Mobile
18114509Notes
18114509Our Groceries
Super User
Posts: 19,850

Re: How to Transform Data Set from Long to Wide Across Multiple Variables

Posted in reply to ndvorak_forrester_com

Do you have a date attached to that day of the week? How do you know which week that Saturday belongs to?

Occasional Contributor
Posts: 9

Re: How to Transform Data Set from Long to Wide Across Multiple Variables

Yes that date is attached to a date, but the data has been summarized for each day of the week by Mean, Sum, and Freq. The numbers representing the Mean, Sum, and Freq variables is Time Spent on the website.

I should have mentioned that the DayofWeek variable is numeric, running from 1=Sunday to 7=Saturday, and is formatted to show the day of the week using the FORMAT downame.

Super User
Posts: 19,850

Re: How to Transform Data Set from Long to Wide Across Multiple Variables

Posted in reply to ndvorak_forrester_com

So you'll only have one day of the week for each company?

EDIT: SAS Learning Module: Reshaping data long to wide using the data step

Occasional Contributor
Posts: 9

Re: How to Transform Data Set from Long to Wide Across Multiple Variables

I will need to summarize for each day of the week by company but for this example you are correct, only one day of the week for each company

Occasional Contributor
Posts: 9

Re: How to Transform Data Set from Long to Wide Across Multiple Variables

Posted in reply to ndvorak_forrester_com

Here is another segment of the dataset where you will see Websites repeating for certain days of the week. A row is identified by a unique DayofWeek, User, and Website combination:

DayofWeekUserWebsiteMeanSumFreq
Saturday243736Bonus Box Tops15151
Saturday243736Flip Chip601.55661711
Saturday243736MyChart1001001
Saturday243736PCH Sweeps35351
Sunday243736Flip Chip158.67935
Sunday243736PCH Sweeps107.333223
Monday243736Bonus Box Tops10202
Monday243736Cash Slots31.544114
Monday243736Flip Chip273273010
Monday243736Graphing Calculator669.3320083
Monday243736Hot Light93.51872
Monday243736PokerHouse507.510152
Monday243736Tri-Peaks Solitare493.59872
Tuesday243736Battle Nations820.6724623
Tuesday243736Hot Light15151
Tuesday243736PCH Sweeps1013033
Tuesday243736Slots Universe741.6722253
Tuesday243736Treasure Diving510.831481429
Wednesday243736Bonus Box Tops30301
Wednesday243736Cash Slots91.286191721
Wednesday243736Castle Clash600.694325072
Wednesday243736Lucktastic8098091
Wednesday243736PCH Sweeps25502
Wednesday243736Slots Universe25502
Wednesday243736Treasure Diving573.513039653
Thursday243736Bonus Box Tops551
Thursday243736Flip Chip311.75374112
Thursday243736Hot Light640.512812
Thursday243736Lucktastic1211211
Thursday243736MyChart2372371
Thursday243736PCH Sweeps155.56224
Thursday243736Treasure Diving738.352288931
Thursday243736Tri-Peaks Solitare238.259534
Friday243736Flip Chip671.6740306
Friday243736Hot Light9829821
Friday243736PCH Sweeps1811811
Friday243736Tri-Peaks Solitare164.53292
Sunday398526Bloomberg23231
Monday398526Blackhawks35351
Saturday398942Spider Free221
Friday398942Spider Free30301
Sunday429762DownloaderFREE1891891
Sunday429762autoshutter1271271
Monday429762Angry Birds Star Wa2902901
Monday429762Toysrus111
Tuesday429762Angry Birds Star Wa158815881
Super User
Posts: 19,850

Re: How to Transform Data Set from Long to Wide Across Multiple Variables

Posted in reply to ndvorak_forrester_com

The link above gives a fairly detailed example on how to accomplish this.

SAS Learning Module: Reshaping data long to wide using the data step

Occasional Contributor
Posts: 9

Re: How to Transform Data Set from Long to Wide Across Multiple Variables

So here is what I came up with, but because a row is identified by the combination of the UserID and AppName, it does not work. I also think there must be a better way to transpose this data.

PROC SORT DATA=WORK.wave1appdow;

  BY AppName UserID;

RUN;

DATA transtest3;

  SET wave1appdow;

  BY AppName UserID;

  KEEP AppName UserID

       Mean_1-Mean_7

       Sum_1-Sum_7

       Freq_1-Freq_7

   

  RETAIN Mean_1-Mean_7

       Sum_1-Sum_7

       Freq_1-Freq_7

  ARRAY AppTimeMean (1:7) Mean_1-Mean_7;

  ARRAY AppTimeSum (1:7) Sum_1-Sum_7;

  ARRAY AppTimeFreq (1:7) N_1-N_7;

  IF first.Appname THEN

       DO;

            DO i = 1 - 7;

            AppTimeMean ( i )  = 0;

            AppTimeFreq  ( i )  = 0;

            AppTimeMed  ( i )  = 0;

            END;

       END;

       AppTimeMean (D_DayofWeek) = Mean;

       AppTimeSum (D_DayofWeek) = Sum;

       AppTimeFreq (D_DayofWeek) = Freq;

  IF last.Appname THEN OUTPUT;

  RUN;

Any Suggestions?

Respected Advisor
Posts: 3,799

Re: How to Transform Data Set from Long to Wide Across Multiple Variables

Posted in reply to ndvorak_forrester_com

DO i = 1 - 7;

That does not do what you think.  You need DO I=1 TO 7;

Super User
Super User
Posts: 7,070

Re: How to Transform Data Set from Long to Wide Across Multiple Variables

Posted in reply to ndvorak_forrester_com

Did you generate the statistics using SAS?  If so then perhaps it would be easier if you generated them in a more vertical format.

DayofWeekUserWebsiteStatValue
Saturday17306100MiKandiMEAN10
Friday17306100TwitterMEAN75

Then you can use PROC TRANSPOSE;

proc transpose data=have out=want ;

by user website ;

id stat dayofweek;

var value;

run;

Ask a Question
Discussion stats
  • 9 replies
  • 439 views
  • 0 likes
  • 4 in conversation