BookmarkSubscribeRSS Feed
mauri0623
Quartz | Level 8

Hello,

The attached file show two tables. I am trying to use proc transpose and transpose the table A into table B. Thank you for your great assistant.

 

-mauri

7 REPLIES 7
ballardw
Super User

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.

 

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the <> icon or attached as text to show exactly what you have and that we can test code against.

 

 

What have you tried so far?

mauri0623
Quartz | Level 8
Table A
fy DST_Name totalSE totalRD fttg
2014 All Institution 2624024.488 1460889.476 126428.746
2015 All Institution 2852550.170 1539595.556 138213.344
2016 All Institution 2697110.604 1570449.914 125886.366
2014 Aaniiih Nakoda C. 309.655 0.000 209.655
2014 Adams State U. 75.000 75.000 0.000
2014 Alabama A&M U. 6632.544 3379.987 134.443
2014 Alcorn State U. 6282.778 3290.692 0.000
2014 Alfred U. 40.000 40.000 0.000
2015 Aaniiih Nakoda C. 309.655 0.000 209.655
2015 Adams State U. 75.000 75.000 0.000
2015 Alabama A&M U. 6632.544 3379.987 134.443
2015 Alcorn State U. 6282.778 3290.692 0.000
2015 Alfred U. 40.000 40.000 0.000
2016 Aaniiih Nakoda C. 309.655 0.000 209.655
2016 Adams State U. 75.000 75.000 0.000
2016 Alabama A&M U. 6632.544 3379.987 134.443
2016 Alcorn State U. 6282.778 3290.692 0.000
2016 Alfred U. 40.000 40.000 0.000

Table B

Dst_name 2014 2015 2016
All Institution 2624024.488 1460889.476 126428.746
All Institution 2852550.170 1539595.556 138213.344
All Institution 2697110.604 1570449.914 125886.366
Aaniiih Nakoda C. 309.655 0.000 209.655
Adams State U. 75.000 75.000 0.000
Alabama A&M U. 6632.544 3379.987 134.443
Alcorn State U. 6282.778 3290.692 0.000
Alfred U. 40.000 40.000 0.000
Aaniiih Nakoda C. 309.655 0.000 209.655
Adams State U. 75.000 75.000 0.000
Alabama A&M U. 6632.544 3379.987 134.443
Alcorn State U. 6282.778 3290.692 0.000
Alfred U. 40.000 40.000 0.000
Aaniiih Nakoda C. 309.655 0.000 209.655
Adams State U. 75.000 75.000 0.000
Alabama A&M U. 6632.544 3379.987 134.443
Alcorn State U. 6282.778 3290.692 0.000
Alfred U. 40.000 40.000 0.000

ballardw
Super User

SAS by default will not use a number as a variable name. Variable names should start with a letter or the _ character and compose of letters, digits or the _. You have to get into setting system options and then using ugly names like '2014'n for these.

 

Second, you apparently are showing a column 2014 with values from multiple years under it. That does not make much sense in any way. Or just didn't move the values as really intended?

Since Aaniiih Nakoda C has no 2015 or 2016 data it would not make much sense to have a 2015 or 2016 value in any case.

 

How will you use the "transposed" data?

mauri0623
Quartz | Level 8

 

How to turn table A into Table B using proc transpose.

 

 

Table A   
fyDST_NametotalSEtotalRDfttg
2014All Institution2624024.4881460889.476126428.746
2015All Institution2852550.1701539595.556138213.344
2016All Institution2697110.6041570449.914125886.366
2014Aaniiih Nakoda C.309.6550.000209.655
2014Adams State U.75.00075.0000.000
2014Alabama A&M U.6632.5443379.987134.443
2014Alcorn State U.6282.7783290.6920.000
2014Alfred U.40.00040.0000.000
2015Aaniiih Nakoda C.309.6550.000209.655
2015Adams State U.75.00075.0000.000
2015Alabama A&M U.6632.5443379.987134.443
2015Alcorn State U.6282.7783290.6920.000
2015Alfred U.40.00040.0000.000
2016Aaniiih Nakoda C.309.6550.000209.655
2016Adams State U.75.00075.0000.000
2016Alabama A&M U.6632.5443379.987134.443
2016Alcorn State U.6282.7783290.6920.000
2016Alfred U.40.00040.0000.000
     
Table B   
     
Dst_name201420152016
All Institution2624024.4881460889.476126428.746 
All Institution2852550.1701539595.556138213.344 
All Institution2697110.6041570449.914125886.366 
Aaniiih Nakoda C.309.6550.000209.655 
Adams State U.75.00075.0000.000 
Alabama A&M U.6632.5443379.987134.443 
Alcorn State U.6282.7783290.6920.000 
Alfred U.40.00040.0000.000 
Aaniiih Nakoda C.309.6550.000209.655 
Adams State U.75.00075.0000.000 
Alabama A&M U.6632.5443379.987134.443 
Alcorn State U.6282.7783290.6920.000 
Alfred U.40.00040.0000.000 
Aaniiih Nakoda C.309.6550.000209.655 
Adams State U.75.00075.0000.000 
Alabama A&M U.6632.5443379.987134.443 
Alcorn State U.6282.7783290.6920.000 
Alfred U.40.00040.0000.000 
maguiremq
SAS Super FREQ
data have;
infile datalines delimiter = ",";
input fy dst_name :$25. totalSE totalRD fttg;
datalines;
2014,All Institution,2624024.488,1460889.476,126428.746
2015,All Institution,2852550.17,1539595.556,138213.344
2016,All Institution,2697110.604,1570449.914,125886.366
2014,Aaniiih Nakoda C.,309.655,0,209.655
2014,Adams State U.,75,75,0
2014,Alabama A&M U.,6632.544,3379.987,134.443
2014,Alcorn State U.,6282.778,3290.692,0
2014,Alfred U.,40,40,0
2015,Aaniiih Nakoda C.,309.655,0,209.655
2015,Adams State U.,75,75,0
2015,Alabama A&M U.,6632.544,3379.987,134.443
2015,Alcorn State U.,6282.778,3290.692,0
2015,Alfred U.,40,40,0
2016,Aaniiih Nakoda C.,309.655,0,209.655
2016,Adams State U.,75,75,0
2016,Alabama A&M U.,6632.544,3379.987,134.443
2016,Alcorn State U.,6282.778,3290.692,0
2016,Alfred U.,40,40,0
;
run;

/* Sort by what you want transposed */

proc sort data = have;
	by dst_name;
run;

/* ID identifies each column. Note that you only have numbers here (goes against SAS naming convention), so SAS puts an underscore in front of the variables */

proc transpose data = have 
	out = want (drop = _name_);
	id fy;
	by dst_name;
run;
dst_name _2014 _2015 _2016 
Aaniiih Nakoda C. 309.66 309.66 309.66 
Aaniiih Nakoda C. 0.00 0.00 0.00 
Aaniiih Nakoda C. 209.66 209.66 209.66 
Adams State U. 75.00 75.00 75.00 
Adams State U. 75.00 75.00 75.00 
Adams State U. 0.00 0.00 0.00 
Alabama A&M U. 6632.54 6632.54 6632.54 
Alabama A&M U. 3379.99 3379.99 3379.99 
Alabama A&M U. 134.44 134.44 134.44 
Alcorn State U. 6282.78 6282.78 6282.78 
Alcorn State U. 3290.69 3290.69 3290.69 
Alcorn State U. 0.00 0.00 0.00 
Alfred U. 40.00 40.00 40.00 
Alfred U. 40.00 40.00 40.00 
Alfred U. 0.00 0.00 0.00 
All Institution 2624024.49 2852550.17 2697110.60 
All Institution 1460889.48 1539595.56 1570449.91 
All Institution 126428.75 138213.34 125886.37 

 

ballardw
Super User

Duplicate post merged.

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 975 views
  • 0 likes
  • 4 in conversation