topic Re: The table to be changed in SAS Programming

The table to be changed

mauri0623 — Thu, 03 Jun 2021 14:49:10 GMT

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

Re: The table to be changed

ballardw — Thu, 03 Jun 2021 15:10:53 GMT

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-data-AKA-generate/ta-p/258712 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?

Re: The table to be changed

mauri0623 — Thu, 03 Jun 2021 15:19:56 GMT

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

Chanaging tables

mauri0623 — Thu, 03 Jun 2021 15:31:53 GMT

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

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

Re: The table to be changed

Kurt_Bremser — Thu, 03 Jun 2021 15:42:34 GMT

Looking at "All Institution", there is no transpose, just a renaming of the columns, and dropping the fy column..

Re: The table to be changed

ballardw — Thu, 03 Jun 2021 15:44:19 GMT

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?

Re: Chanaging tables

maguiremq — Thu, 03 Jun 2021 15:51:14 GMT

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

Re: Chanaging tables

ballardw — Thu, 03 Jun 2021 15:52:53 GMT

Duplicate post merged.