Hi I have a table like this:
Country | Fruit | Fruit Code | Count of Fruit | FY |
201516 | 201617 | 201718 | 201819 | 201920 | 202021 |
Canada | Apple | 001 | 34 | 201617 | 0 | 34 | 39 | 43 | 36 | 0 |
Canada | Apple | 001 | 39 | 201718 | 0 | 34 | 39 | 43 | 36 | 0 |
Canada | Apple | 001 | 43 | 201819 | 0 | 34 | 39 | 43 | 36 | 0 |
Canada | Apple | 001 | 36 | 201920 | 0 | 34 | 39 | 43 | 36 | 0 |
Country | Fruit | Fruit Code |
201516 | 201617 | 201718 | 201819 | 201920 | 202021 |
Canada | Apple | 001 | 0 | 34 | 39 | 43 | 36 | 0 |
and it want it to look like this using proc transpose:
I have a table like this:
Your first table already has the data that you claim you want in the transposed table. So I don't understand your request.
Also, please note that the calendar variable names are not legal variable names in SAS.
Also, please note that using calendar information in variable names almost always makes the subsequent programming more difficult, and you would probably be much better off leaving your data in a long data set, not a wide data set.
Keeping Country, Fruit, Fruit Code, CountOfFruit and FY and changing FY to be a proper sas-date are things i would to get a useful dataset. The table you want then is something that proc report creates using FY as across-variable.
Elaborating on one of the points that @PaigeMiller made, the top table becomes the bottom table by getting rid of pieces you don't need:
data want;
set have (drop=FY Count_of_Fruit);
by country fruit;
if first.fruit;
run;
If your variable names are actually different, it's easy to modify the program to use the correct names.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.