Help using Base SAS procedures

Proc Format question

Reply
New Contributor
Posts: 4

Proc Format question

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'  .....

Super User
Posts: 10,516

Re: Proc Format question

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'

New Contributor
Posts: 4

Re: Proc Format question

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

Super User
Posts: 10,516

Re: Proc Format question

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.

New Contributor
Posts: 4

Re: Proc Format question

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?

             ;

Super Contributor
Posts: 268

Re: Proc Format question

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?

Super User
Posts: 10,516

Re: Proc Format question

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.

Regular Contributor
Posts: 217

Re: Proc Format question

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.

Ask a Question
Discussion stats
  • 7 replies
  • 365 views
  • 0 likes
  • 4 in conversation