proc transpose output issues

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

proc transpose output issues

My original raw text file data:

Album_ID Yr_2009 Yr_2010 Yr_2011 Yr_2012 Yr_2013 Yr_2014 Yr_2015
A_101 58211 52281 55349 57560 55923 51014 56737
A_102 23853 7898 20976 30713 24116 30221 22435
A_103 62688 64922 78319 69196 70138 70201 65866
A_104 91409 85348 88421 97774 81779 91098 88995
A_105 68384 62371 80365 65344 70042 72032 72781

 

The output I am trying to emulate:

sas1.png

 

The code I have so far: 

libname desktop "C:\Users\Alicia\Desktop\";
data desktop.album_sales_data;
infile "C:\Users\Alicia\Desktop\BIOS623Fa2016_02_A05_album_sales_data.txt" dlm="09"x firstobs=2;
input Album_ID $ Yr_2009 Yr_2010 Yr_2011 Yr_2012 Yr_2013 Yr_2014 Yr_2015;
run;
proc sort data= desktop.album_sales_data;
by Album_ID;
run;
proc transpose data= desktop.album_sales_data out=album_sales_3yr (rename= (_Name_=Year));
by Album_ID;
run;
proc sort data=album_sales_3yr;
by Year;
run;
proc print data= album_sales_3yr;
run;

However, if I try to rename COL1 to "Sales_Amt," it no longer recognizes the variable "year." I've also tried several commands to delete those observations for which year= Yr_2009-Yr_2012, but not of those seem to work. Any general guidance would be greatly appreciated.

 


Accepted Solutions
Solution
‎12-05-2016 02:42 PM
Super User
Posts: 5,369

Re: proc transpose output issues

[ Edited ]

While it's certainly possible to debug this and figure out what's wrong, it's probably easier to start over:

 

data want;

set have;

array yrs {2009:2015} yr_2009 - yr_2015;

do _n_=2013 to 2015;

   Amount = yrs{_n_};

   Year = vname(yrs{_n_});

   output;

end;

keep Year Album_ID Amount;

run;

 

proc sort data=want;

by Year Album_ID;

run;

View solution in original post


All Replies
Occasional Contributor
Posts: 13

Re: proc transpose output issues


This is the output that I am stuck at:

 Album_ID Year COL1 
A_101Yr_200958211
A_102Yr_200923853
A_103Yr_200962688
A_104Yr_200991409
A_105Yr_200968384
A_101Yr_201052281
A_102Yr_20107898
A_103Yr_201064922
A_104Yr_201085348
A_105Yr_201062371
A_101Yr_201155349
A_102Yr_201120976
A_103Yr_201178319
A_104Yr_201188421
A_105Yr_201180365
A_101Yr_201257560
A_102Yr_201230713
A_103Yr_201269196
A_104Yr_201297774
A_105Yr_201265344
A_101Yr_201355923
A_102Yr_201324116
A_103Yr_201370138
A_104Yr_201381779
A_105Yr_201370042
A_101Yr_201451014
A_102Yr_201430221
A_103Yr_201470201
A_104Yr_201491098
A_105Yr_201472032
A_101Yr_201556737
A_102Yr_201522435
A_103Yr_201565866
A_104Yr_201588995
A_105Yr_201572781
Solution
‎12-05-2016 02:42 PM
Super User
Posts: 5,369

Re: proc transpose output issues

[ Edited ]

While it's certainly possible to debug this and figure out what's wrong, it's probably easier to start over:

 

data want;

set have;

array yrs {2009:2015} yr_2009 - yr_2015;

do _n_=2013 to 2015;

   Amount = yrs{_n_};

   Year = vname(yrs{_n_});

   output;

end;

keep Year Album_ID Amount;

run;

 

proc sort data=want;

by Year Album_ID;

run;

Occasional Contributor
Posts: 13

Re: proc transpose output issues

I'm still having a bit of trouble at the transpose step. So far I have:

libname desktop "C:\Users\Alicia\Desktop\";
data desktop.album_sales_data;
infile "C:\Users\Alicia\Desktop\BIOS623Fa2016_02_A05_album_sales_data.txt" dlm="09"x firstobs=2;
input Album_ID $ Yr_2009 Yr_2010 Yr_2011 Yr_2012 Yr_2013 Yr_2014 Yr_2015;
run;
data transpose_data;
set desktop.album_sales_data;
array yrs{2009:2015} yr_2009-yr_2015;
do _n_= 2013 to 2015;
Sales_Amt = yrs{_n_};
year=vname(yrs{_n_});
output;
end;
drop yr_2009-yr_2012
run;
proc sort data= transpose_data;
by year;
run;
proc print data=transpose_data;
run;

which gives me an output of:

tp1.JPG

So for my proc transpose step,

I want to transpose by sales_amt?

Super User
Posts: 5,369

Re: proc transpose output issues

Except for keeping a few extra variables, this output is exactly what you said you were trying to emulate.  How would you like to change it?

Respected Advisor
Posts: 4,820

Re: proc transpose output issues

[ Edited ]

You can do the renaming in the last proc sort:

 

proc sort data=album_sales_3yr out=album_sales_3yr(rename=col1=Sales_Amt);
where year not in ("Yr_2009","Yr_2010","Yr_2011","Yr_2012"); by Year; run;
PG
☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 227 views
  • 1 like
  • 3 in conversation