BookmarkSubscribeRSS Feed
eramirez
Fluorite | Level 6

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;

 

5 REPLIES 5
Reeza
Super User
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
Fluorite | Level 6

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
Super User
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
Fluorite | Level 6
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
Super User
How does SAS know percent1 maps to 60601 and percent9 maps to 90210 for example? Do you have a lookup table with that information?

sas-innovate-2024.png

 

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 460 views
  • 0 likes
  • 2 in conversation