BookmarkSubscribeRSS Feed
joeyyyyyg
Calcite | Level 5

I'm trying to make a crosstab on 2011/2013 Education. Below are named values for each respective year.

value $EDULEV   /* 2011 Education*/

          '00' = 'No formal education'

          '01' = 'First grade'

          '02' = 'Second grade'

          '03' = 'Third grade'

          '04' = 'Fourth grade'

          '05' = 'Fifth grade'

          '06' = 'Sixth grade (grammar sch. graduate)'

             ;

value $EDULEV   /*2013 Education*/

          '00' = 'No formal education'

   '01' = 'Pre-Kindergarten' /*New Selection*/

   '02' = 'Kindergarten' /*New Selection*/

          '03' = 'First grade'

          '04' = 'Second grade'

          '05' = 'Third grade'

          '06' = 'Fourth grade'

          '07' = 'Fifth grade'

          '08' = 'Sixth grade (grammar sch. graduate)'

I need help re configuring these values to include Pre-kindergarten and Kindergarten. My log notifies me that values overlap and it cannot process.

What method should I use to approach and resolve this? Below is my current code and I know it needs tweaking.

proc format;

value $EduLevel_

'00'='No Formal Education'

'011'='Pre-Kindergarten'

'012'='Kindergarten'

'01'='First Grade'

'03'='First Grade'

'02'='Second Grade'

'04'='Second Grade'

'03'='Third Grade'

'05'='Third Grade'

'04'='Fourth Grade'

'06'='Fourth Grade'

'05'='Fifth Grade'

'07'='Fifth Grade'

'06'='Sixth Grade'

'08'='Sixth Grade'  .....

7 REPLIES 7
ballardw
Super User

If this were my project I would be more likely to standardize the variable, basically add 2 to the not "00" in the 2011 data, creating a new variable with an appropriate label. Especially if the "new" coding is likely to continue into the future I would standardize on the 2013 data and format.

Your approach would also require a new variable to assign the new codes.

In a value format a value may only appear once on the left of any = signs. Your format will fail due to assigning multiple values for '03' of First grade and Third grade, and similar for '04', '05' and '06'

joeyyyyyg
Calcite | Level 5

I have two imported lib statements from each, 2011 and 2013. I can't really go in and alter their formatting of the raw data; I need help figuring out how to re-construct the formats so that they will align after including '01' Pre-kindergarten and '02' Kindergarten

ballardw
Super User

Not going to happen, one value such as 03 is only ever going to have a single result from proc format.

HOW were you going to get values such that this would possibly work?

'011'='Pre-Kindergarten'

'012'='Kindergarten'

That would have required either ADDING a variable, which doesn't change any of the raw data and was my suggestion

OR

would requiring modifying your raw data which you say you can't do.

joeyyyyyg
Calcite | Level 5

These are the variables given for each respective year in 2011 and 2013....

/* 2011 Education*/

          '00' = 'No formal education'

          '01' = 'First grade'

          '02' = 'Second grade'

          '03' = 'Third grade'

          '04' = 'Fourth grade'

          '05' = 'Fifth grade'

          '06' = 'Sixth grade (grammar sch. graduate)'

          '07' = 'Seventh grade'

          '08' = 'Eighth grade'

          '09' = 'Ninth grade'

          '10' = 'Tenth grade'

          '11' = 'Eleventh grade'

          '12' = 'Twelfth grade (no diploma)'

          '13' = 'HS Diploma or GED'

          '14' = 'Business, technical training'

          '15' = 'Some college, no degree'

          '16' = 'Associate degree'

          '17' = 'Bachelor degree'

          '18' = 'Graduate degree'

          '19' = 'Other'

          '99' = 'Unknown'

             ;

  /*2013 Education*/

'00' = 'No formal education'

'01' = 'Pre-Kindergarten' /*New Selection*/

'02' = 'Kindergarten' /*New Selection*/

'03' = 'First grade'

'04' = 'Second grade'

'05' = 'Third grade'

'06' = 'Fourth grade'

'07' = 'Fifth grade'

'08' = 'Sixth grade (grammar sch. graduate)'

'09' = 'Seventh grade'

'10' = 'Eighth grade'

'11' = 'Ninth grade'

'12' = 'Tenth grade'

'13' = 'Eleventh grade'

'14' = 'Twelfth grade (no diploma)'

'15' = 'HS Diploma or GED'

'16' = 'Business, technical training'

'17' = 'Some college, no degree'

'18' = 'Associate degree'

'19' = 'Bachelor degree'

'20' = 'Graduate degree'

'21' = 'Other'

'99' = 'Unknown'

I need to somehow redefine these variables to include '01'=Pre-kindergarten and '02'=Kindergarten in 2013 (that does not exist in 2011).

Yes there is overlap with same variables producing an ERROR. How do I fix this?

             ;

GreggB
Pyrite | Level 9

Can you use 2 separate proc formats, one with $edulevel_11 and one with $edulevel13?  Then, apply the formats separately to each data set that you're reading in from the two libname statements?

ballardw
Super User

I have no idea what you mean by two libname statements?

I think you need to post some examples of starting data and what you want the final result to look like.

Most approaches with SAS are going to require the data to be in a single data set to preform any kind of "cross tab" so some data clarification is in order.

jwillis
Quartz | Level 8

Joeyyyyyg,

From experience, the character values of "01","011", and "012" are being read as the same values.  If your variable is a length of 2, or your format length is 2, then SAS is truncating the format or variable values to "01" and then telling you that there is an overlap of the variables.  "01","011", and "012" only work as format values if your data contains "01","011", and "012" and is 3 character positions long.   I like the idea suggested by Gregg Bibb  of using one format for 2011 data and another for 2013 data.

Since 2011 does not have the values:

'01' = 'Pre-Kindergarten' /*New Selection*/

'02' = 'Kindergarten' /*New Selection*/

you have to create a new format that converts 2011 values to 2013 formatted values.   Ex: format.....'01' = '03 '02' = '04' etc...  Only then can you compare 2011 rows to 2013 rows.  Obviously the 2013 rows for '01' and '02' will not have matching values to any 2011 reformatted rows. I would create a new variable in the 2011 file that carries the 2013 formatted value.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 7 replies
  • 1120 views
  • 0 likes
  • 4 in conversation