BookmarkSubscribeRSS Feed
ndvorak_forrester_com
Calcite | Level 5

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
9 REPLIES 9
Reeza
Super User

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

ndvorak_forrester_com
Calcite | Level 5

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.

ndvorak_forrester_com
Calcite | Level 5

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

ndvorak_forrester_com
Calcite | Level 5

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
ndvorak_forrester_com
Calcite | Level 5

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?

data_null__
Jade | Level 19

DO i = 1 - 7;

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

Tom
Super User Tom
Super User

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;

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
  • 9 replies
  • 1439 views
  • 0 likes
  • 4 in conversation