topic Re: Proc transpose using array in SAS Procedures

Proc transpose using array

eramirez — Tue, 25 Aug 2020 17:13:45 GMT

Hello

I am trying to transpose a wide data set to a long dataset and found some sas code that uses an array; I tested it out using three variables and it worked well. The issue I have is that the zip codes are not all consecutive, for example, I have 60601 to 60620, but then it jumps to 60625 to 60640; most are consecutive but several are not. How do I specify that in the array?

Thank you!

data WORK.TRANSPOSE;
  infile datalines dsd truncover;
  input Date:DATE9. Percent:32. Percent1:32. Percent2:32.;
  format Date DATE9.;
  datalines;
01JAN2020 16.667 0 0
02JAN2020 0 0 0
03JAN2020 14.286 0 0
04JAN2020 0 0 0
05JAN2020 0 0 0
06JAN2020 0 0 0
07JAN2020 0 0 0
08JAN2020 0 0 0
09JAN2020 20 0 0
10JAN2020 0 0 0
11JAN2020 0 0 0
12JAN2020 0 0 0
13JAN2020 0 0 0
14JAN2020 0 0 0
15JAN2020 0 0 0
16JAN2020 25 0 0
17JAN2020 0 0 0
18JAN2020 0 0 0
19JAN2020 0 0 0
20JAN2020 0 0 0
;;;;

data Transpose2; set transpose;

	array apctcli(60601:60603) Percent Percent1 Percent2;

	do zip = 60601 to 60603;
	pctcli =  apctcli(zip);
	output;
	END;

	DROP Percent Percent1 Percent2;
	run;

Re: Proc transpose using array

Reeza — Tue, 25 Aug 2020 17:17:15 GMT

I would argue this isn't a good way to transpose. You're relying on location of the data to be the correct variable?

How do you know percent1 maps to 60601?
Where is that information stored?

Re: Proc transpose using array

eramirez — Tue, 25 Aug 2020 17:29:48 GMT

Thanks for the reply. You are correct, I am relying on location, but in this case, Percent will always be 60601, Percent1 will always be 60602 and so on and they will always be in the same location. It is based on a data dictionary. Unfortunately, that is how the data is downloaded and sent to me. Actually, there are five values associated with each zip code and numbered the same way. For example, there is Percent1, Count1, Expected1, and all are associated with 60602. It's annoying, but it's what I have to work with. If you have a more efficient way to transpose I am definitely open to that! Thanks.

Re: Proc transpose using array

Reeza — Tue, 25 Aug 2020 17:33:30 GMT

How does SAS know that though? Are you manually coding that lookup or is it just in a text file or in your head?
You need to find a way to make that part of your process which means making it part of your program in some fashion which means getting the data into SAS somehow.

Re: Proc transpose using array

eramirez — Tue, 25 Aug 2020 18:00:53 GMT

I am not really following, I'm sorry. How does SAS know what? The data was imported into SAS and it's a SAS dataset with those variable names, except for Zip which I created.

Re: Proc transpose using array

Reeza — Tue, 25 Aug 2020 18:24:06 GMT

How does SAS know percent1 maps to 60601 and percent9 maps to 90210 for example? Do you have a lookup table with that information?