How to program a Macro to assign the number to GEO_JUR column corrospored to the State name?

Reply
Super Contributor
Posts: 274

How to program a Macro to assign the number to GEO_JUR column corrospored to the State name?

Hello:

 

I would like to program a Macro procedure when the State name shown in the State column, it will corrospored to assign a certain number to the GEO_JUR column.  Therefore, if the same situation happens again, it will save my time.  Please see my SAS data.

However, I don't know how.   Please help.  Thanks.

 

Ying

 

%macro ASSIGNGEO(&State,GEO_JUR);
length GEO_JUR $10.;
IF &State="Alabama" THEN GEO_JUR="01";
IF &State="Alaska" THEN GEO_JUR="02";
IF &State="Arizona" THEN GEO_JUR="04";
IF &State="Arkansas" THEN GEO_JUR="05";
IF &State="California" THEN GEO_JUR="06";
IF &State="Colorado" THEN GEO_JUR="08";
IF &State="Connecticut" THEN GEO_JUR="09";
IF &State="Delaware" THEN GEO_JUR="10";
IF &State="District of Columbia" THEN GEO_JUR="11";
IF &State="Florida" THEN GEO_JUR="12";
IF &State="Georgia" THEN GEO_JUR="13";
IF &State="Hawaii" THEN GEO_JUR="15";
IF &State="Idaho" THEN GEO_JUR="16";
IF &State="Illinois" THEN GEO_JUR="17";
IF &State="Illinios" THEN GEO_JUR="17";
IF &State="Indiana" THEN GEO_JUR="18";
IF &State="Iowa" THEN GEO_JUR="19";
IF &State="Kansas" THEN GEO_JUR="20";
IF &State="Kentucky" THEN GEO_JUR="21";
IF &State="Louisiana" THEN GEO_JUR="22";
IF &State="Maine" THEN GEO_JUR="23";
IF &State="Maryland" THEN GEO_JUR="24";
IF &State="Massachusetts" THEN GEO_JUR="25";
IF &State="Michigan" THEN GEO_JUR="26";
IF &State="Minnesota" THEN GEO_JUR="27";
IF &State="Mississippi" THEN GEO_JUR="28";
IF &State="Missouri" THEN GEO_JUR="29";
IF &State="Montana" THEN GEO_JUR="30";
IF &State="Nebraska" THEN GEO_JUR="31";
IF &State="Nevada" THEN GEO_JUR="32";
IF &State="New Hampshire" THEN GEO_JUR="33";
IF &State="New Jersey" THEN GEO_JUR="34";
IF &State="New Mexico" THEN GEO_JUR="35";
IF &State="New York" THEN GEO_JUR="36";
IF &State="North Carolina" THEN GEO_JUR="37";
IF &State="North Dakota" THEN GEO_JUR="38";
IF &State="Ohio" THEN GEO_JUR="39";
IF &State="Oklahoma" THEN GEO_JUR="40";
IF &State="Oregon" THEN GEO_JUR="41";
IF &State="Pennsylvania" THEN GEO_JUR="42";
IF &State="Puerto Rico" THEN GEO_JUR="72";
IF &State="Rhode Island" THEN GEO_JUR="44";
IF &State="South Carolina" THEN GEO_JUR="45";
IF &State="South Dakota" THEN GEO_JUR="46";
IF &State="Tennessee" THEN GEO_JUR="47";
IF &State="Texas" THEN GEO_JUR="48";
IF &State="United States" THEN GEO_JUR="N1";
IF &State="Utah" THEN GEO_JUR="49";
IF &State="Vermont" THEN GEO_JUR="50";
IF &State="Virginia" THEN GEO_JUR="51";
IF &State="Washington" THEN GEO_JUR="53";
IF &State="West Virginia" THEN GEO_JUR="54";
IF &State="Wisconsin" THEN GEO_JUR="55";
IF &State="Wyoming" THEN GEO_JUR="56";

IF &State="American Samoa" THEN GEO_JUR="60";
IF &State="Guam" THEN GEO_JUR="66";
IF &State="Northern Mariana Islands " THEN GEO_JUR="69";
IF &State="Republic of Palau" THEN GEO_JUR="70";
IF &State="U.S. Virgin Islands " THEN GEO_JUR="78";
IF &State="Virgin Islands " THEN GEO_JUR="78";
IF &State="N1" THEN GEO_JUR="N1";
run;
%Mend;

data GEOMACRO1;
set GEOMACRO;
?????
run;

Grand Advisor
Posts: 17,332

Re: How to program a Macro to assign the number to GEO_JUR column corrospored to the State name?

You need a format not a macro. 

 

I recommend the paper:

http://www2.sas.com/proceedings/sugi30/001-30.pdf

 

Then you can just create the new variable with a PUT function.

 

Respected Advisor
Posts: 4,973

Re: How to program a Macro to assign the number to GEO_JUR column corrospored to the State name?

You might choose a format.  You might choose a macro.  But either way, that is only the beginning of the task.  Whichever one you choose,  you also need to learn:

 

  • How do you save it permanently?
  • How do you make it available to a later program?

These are worthwhile topics, in the long run.  In the short run, however, there are indications that your time would be better spent elsewhere.  For example, looking at your macro attempt:

 

  • Why would a RUN statement be part of a macro definition?  That would mean that wherever you use the macro, it would have to be the last part of your DATA step.
  • Why are there no ELSE statements?  Without them, your code will take twice as long to execute.  Remember, macros execute the same code repeatedly.  If you write inefficient macros, you are destined to execute inefficient code every time.
  • Why does GEO_JUR receive a length of 10?  (There may be legitimate reasons for this, but the code that follows only needs two characters.)

All of these are things you learn along the way.  This isn't a criticism ... it's guidance.  Your time would be better spent learning more basic topics before you start writing macros.

Super Contributor
Posts: 274

Re: How to program a Macro to assign the number to GEO_JUR column corrospored to the State name?

The reason I would like to use Macro is if there is another data set come with State column, I could use Macro to run the 2nd data set. Then combine two of the data set together.   I think if I use FORMAT, my code will be long.   I'm try to save some space.  Eventually, I will delete the State column and only leave GEO_JUR column.

Grand Advisor
Posts: 10,210

Re: How to program a Macro to assign the number to GEO_JUR column corrospored to the State name?

The advantage of the format or informat approach is that all of the code would be replaced by:

 

GEO_JUR = put(&state,State2code.);

 

Note that if you have the 2 character code for the state such as NC for North Carolina that there is an existing function STFIPS that I believe does what you are attempting.

 

Respected Advisor
Posts: 4,973

Re: How to program a Macro to assign the number to GEO_JUR column corrospored to the State name?

OK, here's a compromise that might be more appropriate.  Since it looks like STATE will always be the incoming field name, take these steps:

 

  • Save your 50 or so IF/THEN statements in a separate file.
  • Use that file wherever you would like, by adding one statement to later programs:

%include 'path to the file with the 50 or so statements';

 

 

Super Contributor
Posts: 274

Re: How to program a Macro to assign the number to GEO_JUR column corrospored to the State name?

That looks a great plan.  However, I never learn %include statement before.  I wrote some codes based on your subgustion.  Could you help me to review whether it's right?

 

Many thanks.

 

 

 

Data Training 2;

                set training 1;

IF State="Alabama" THEN GEO_JUR="01";

IF State="Alaska" THEN GEO_JUR="02";

IF State="Arizona" THEN GEO_JUR="04";

IF State="Arkansas" THEN GEO_JUR="05";

IF State="California" THEN GEO_JUR="06";

IF State="Colorado" THEN GEO_JUR="08";

IF State="Connecticut" THEN GEO_JUR="09";

IF State="Delaware" THEN GEO_JUR="10";

IF State="District of Columbia" THEN GEO_JUR="11";

IF State="Florida" THEN GEO_JUR="12";

IF State="Georgia" THEN GEO_JUR="13";

IF State="Hawaii" THEN GEO_JUR="15";

IF State="Idaho" THEN GEO_JUR="16";

IF State="Illinois" THEN GEO_JUR="17";

IF State="Illinios" THEN GEO_JUR="17";

IF State="Indiana" THEN GEO_JUR="18";

IF State="Iowa" THEN GEO_JUR="19";

IF State="Kansas" THEN GEO_JUR="20";

IF State="Kentucky" THEN GEO_JUR="21";

IF State="Louisiana" THEN GEO_JUR="22";

IF State="Maine" THEN GEO_JUR="23";

IF State="Maryland" THEN GEO_JUR="24";

IF State="Massachusetts" THEN GEO_JUR="25";

IF State="Michigan" THEN GEO_JUR="26";

IF State="Minnesota" THEN GEO_JUR="27";

IF State="Mississippi" THEN GEO_JUR="28";

IF State="Missouri" THEN GEO_JUR="29";

IF State="Montana" THEN GEO_JUR="30";

IF State="Nebraska" THEN GEO_JUR="31";

IF State="Nevada" THEN GEO_JUR="32";

IF State="New Hampshire" THEN GEO_JUR="33";

IF State="New Jersey" THEN GEO_JUR="34";

IF State="New Mexico" THEN GEO_JUR="35";

IF State="New York" THEN GEO_JUR="36";

IF State="North Carolina" THEN GEO_JUR="37";

IF State="North Dakota" THEN GEO_JUR="38";

IF State="Ohio" THEN GEO_JUR="39";

IF State="Oklahoma" THEN GEO_JUR="40";

IF State="Oregon" THEN GEO_JUR="41";

IF State="Pennsylvania" THEN GEO_JUR="42";

IF State="Puerto Rico" THEN GEO_JUR="72";

IF State="Rhode Island" THEN GEO_JUR="44";

IF State="South Carolina" THEN GEO_JUR="45";

IF State="South Dakota" THEN GEO_JUR="46";

IF State="Tennessee" THEN GEO_JUR="47";

IF State="Texas" THEN GEO_JUR="48";

IF State="United States" THEN GEO_JUR="N1";

IF State="Utah" THEN GEO_JUR="49";

IF State="Vermont" THEN GEO_JUR="50";

IF State="Virginia" THEN GEO_JUR="51";

IF State="Washington" THEN GEO_JUR="53";

IF State="West Virginia" THEN GEO_JUR="54";

IF State="Wisconsin" THEN GEO_JUR="55";

IF State="Wyoming" THEN GEO_JUR="56";

IF State="American Samoa" THEN GEO_JUR="60";

IF State="Guam" THEN GEO_JUR="66";

IF State="Northern Mariana Islands " THEN GEO_JUR="69";

IF State="Republic of Palau" THEN GEO_JUR="70";

IF State="U.S. Virgin Islands " THEN GEO_JUR="78";

IF State="Virgin Islands " THEN GEO_JUR="78";

IF State="N1" THEN GEO_JUR="N1";

run;

 

Data training 4;

                set training 3;

                %include 'training 2';

run;

 

Data training final;

                set training 2 training 4;

                drop state;

run;

Respected Advisor
Posts: 4,973

Re: How to program a Macro to assign the number to GEO_JUR column corrospored to the State name?

Even simpler.  Create a program called "state_recodes.sas" and save this text within it:

 

 

IF State="Alabama" THEN GEO_JUR="01";

IF State="Alaska" THEN GEO_JUR="02";

IF State="Arizona" THEN GEO_JUR="04";

IF State="Arkansas" THEN GEO_JUR="05";

IF State="California" THEN GEO_JUR="06";

IF State="Colorado" THEN GEO_JUR="08";

IF State="Connecticut" THEN GEO_JUR="09";

IF State="Delaware" THEN GEO_JUR="10";

IF State="District of Columbia" THEN GEO_JUR="11";

IF State="Florida" THEN GEO_JUR="12";

IF State="Georgia" THEN GEO_JUR="13";

IF State="Hawaii" THEN GEO_JUR="15";

IF State="Idaho" THEN GEO_JUR="16";

IF State="Illinois" THEN GEO_JUR="17";

IF State="Illinios" THEN GEO_JUR="17";

IF State="Indiana" THEN GEO_JUR="18";

IF State="Iowa" THEN GEO_JUR="19";

IF State="Kansas" THEN GEO_JUR="20";

IF State="Kentucky" THEN GEO_JUR="21";

IF State="Louisiana" THEN GEO_JUR="22";

IF State="Maine" THEN GEO_JUR="23";

IF State="Maryland" THEN GEO_JUR="24";

IF State="Massachusetts" THEN GEO_JUR="25";

IF State="Michigan" THEN GEO_JUR="26";

IF State="Minnesota" THEN GEO_JUR="27";

IF State="Mississippi" THEN GEO_JUR="28";

IF State="Missouri" THEN GEO_JUR="29";

IF State="Montana" THEN GEO_JUR="30";

IF State="Nebraska" THEN GEO_JUR="31";

IF State="Nevada" THEN GEO_JUR="32";

IF State="New Hampshire" THEN GEO_JUR="33";

IF State="New Jersey" THEN GEO_JUR="34";

IF State="New Mexico" THEN GEO_JUR="35";

IF State="New York" THEN GEO_JUR="36";

IF State="North Carolina" THEN GEO_JUR="37";

IF State="North Dakota" THEN GEO_JUR="38";

IF State="Ohio" THEN GEO_JUR="39";

IF State="Oklahoma" THEN GEO_JUR="40";

IF State="Oregon" THEN GEO_JUR="41";

IF State="Pennsylvania" THEN GEO_JUR="42";

IF State="Puerto Rico" THEN GEO_JUR="72";

IF State="Rhode Island" THEN GEO_JUR="44";

IF State="South Carolina" THEN GEO_JUR="45";

IF State="South Dakota" THEN GEO_JUR="46";

IF State="Tennessee" THEN GEO_JUR="47";

IF State="Texas" THEN GEO_JUR="48";

IF State="United States" THEN GEO_JUR="N1";

IF State="Utah" THEN GEO_JUR="49";

IF State="Vermont" THEN GEO_JUR="50";

IF State="Virginia" THEN GEO_JUR="51";

IF State="Washington" THEN GEO_JUR="53";

IF State="West Virginia" THEN GEO_JUR="54";

IF State="Wisconsin" THEN GEO_JUR="55";

IF State="Wyoming" THEN GEO_JUR="56";

IF State="American Samoa" THEN GEO_JUR="60";

IF State="Guam" THEN GEO_JUR="66";

IF State="Northern Mariana Islands " THEN GEO_JUR="69";

IF State="Republic of Palau" THEN GEO_JUR="70";

IF State="U.S. Virgin Islands " THEN GEO_JUR="78";

IF State="Virgin Islands " THEN GEO_JUR="78";

IF State="N1" THEN GEO_JUR="N1";

 

You will have to be responsible knowing the folder where you saved this file.

 

Then each time you want to use it:

 

data want;

set training1;

%include 'full path to the file including the folder and file name';

run;

Grand Advisor
Posts: 17,332

Re: How to program a Macro to assign the number to GEO_JUR column corrospored to the State name?


ybz12003 wrote:

I think if I use FORMAT, my code will be long.

No. Why do you think so? How do expect to use the macro in your code as is? If your variable is in a data step how do you intend to call the macro?

 

Have you looked into format?

 

  • Formats are defined once
  • Formats can be created from a dataset, no need to manually list code
  • Formats can be exported back out to dataset
  • Formats can be stored permanently into your user library.
  • Formats do not appear in log or need resolving.
  • Formats can be used to recode (PUT() function) or simply change how a variables is displayed 
  • Formats can be used in a data step or proc SQL without any changes required. Your macro will not work in a proc sql step. 

Macro's generate code, and you're treating it a macro as a function - it's not. You can create macro style functions but it's not recommended. You can create functions using FCMP. 

 

PROC FCMP is another option, which is still better than a macro in your situation. 

 

@ballardw the FIPS family of function does not appear to exist in SAS 9.4 - not sure why.

 

proc format;
value $ state_code
'Alabama' = '01'
'Alaska' = '02'
'Arizona' = '04'
'Arkansas' = '05'
'California' = '06'
'Colorado' = '08'
'Connecticut' = '09'
'Delaware' = '10'
'District of Columbia' = '11'
'Florida' = '12'
'Georgia' = '13'
'Hawaii' = '15'
'Idaho' = '16'
'Illinois' = '17'
'Illinios' = '17'
'Indiana' = '18'
'Iowa' = '19'
'Kansas' = '20'
'Kentucky' = '21'
'Louisiana' = '22'
'Maine' = '23'
'Maryland' = '24'
'Massachusetts' = '25'
'Michigan' = '26'
'Minnesota' = '27'
'Mississippi' = '28'
'Missouri' = '29'
'Montana' = '30'
'Nebraska' = '31'
'Nevada' = '32'
'New Hampshire' = '33'
'New Jersey' = '34'
'New Mexico' = '35'
'New York' = '36'
'North Carolina' = '37'
'North Dakota' = '38'
'Ohio' = '39'
'Oklahoma' = '40'
'Oregon' = '41'
'Pennsylvania' = '42'
'Puerto Rico' = '72'
'Rhode Island' = '44'
'South Carolina' = '45'
'South Dakota' = '46'
'Tennessee' = '47'
'Texas' = '48'
'United States' = 'N1'
'Utah' = '49'
'Vermont' = '50'
'Virginia' = '51'
'Washington' = '53'
'West Virginia' = '54'
'Wisconsin' = '55'
'Wyoming' = '56'
'American Samoa' = '60'
'Guam' = '66'
'Northern Mariana Islands ' = '69'
'Republic of Palau' = '70'
'U.S. Virgin Islands ' = '78'
'Virgin Islands ' = '78'
'N1' = 'N1';
run;

data check;
input state $20.;
state_code = put(state, $state_code.);
cards;
Alabama
Virginia
N1
American Samoa
;
run;

 

 

Super Contributor
Posts: 274

Re: How to program a Macro to assign the number to GEO_JUR column corrospored to the State name?

The problem is the GEO_JUR is correspored with State name, please check the Excel sheet I attached.  The State column will be deleted eventually. 

Grand Advisor
Posts: 17,332

Re: How to program a Macro to assign the number to GEO_JUR column corrospored to the State name?

I'm not sure I follow? I used the your macro code exactly. I don't download files in general. 

 

If your codes will vary for some reason, create multiple formats and use Putc to recode. 

Grand Advisor
Posts: 17,332

Re: How to program a Macro to assign the number to GEO_JUR column corrospored to the State name?

Did you run the code, if so, how does it not work? 

Super Contributor
Posts: 274

Re: How to program a Macro to assign the number to GEO_JUR column corrospored to the State name?

The procedure you wrote is to assign the number to the "State" column, and save it.   However, I want to assign the number to the "GEO_JUR" varible based on the State column.   Then delete the "State" variable.   Somehow, you misunderstand my intension.   But I'm still appreciated your effort.

Grand Advisor
Posts: 17,332

Re: How to program a Macro to assign the number to GEO_JUR column corrospored to the State name?

You're kidding me right?

 

 

data GEOMACRO1;
set GEOMACRO;

GEO_JUR=put(state, $state_code.);

drop state;
run;

 

 

 

 

Ask a Question
Discussion stats
  • 13 replies
  • 834 views
  • 3 likes
  • 4 in conversation