Proc transpose using array

[eramirez]

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;

[Reeza]

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?

[eramirez]

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.

[Reeza]

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.

[eramirez]

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.

[Reeza]

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