topic Re: Proc transpose without duplicates in SAS Programming

Proc transpose without duplicates

mtee15 — Mon, 13 Dec 2021 13:59:28 GMT

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

and it want it to look like this using proc transpose:

Re: Proc transpose without duplicates

PaigeMiller — Mon, 13 Dec 2021 14:09:05 GMT

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.

Re: Proc transpose without duplicates

andreas_lds — Mon, 13 Dec 2021 14:30:33 GMT

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.

Re: Proc transpose without duplicates

Astounding — Mon, 13 Dec 2021 15:02:06 GMT

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.