DATA Step, Macro, Functions and more

Matching Formats in order to stack data sets with a set function

Reply
Contributor
Posts: 32

Matching Formats in order to stack data sets with a set function

 I am trying to stack three data sets that are almost identical, however, some of the variables are character when the same variable another dataset is numberic. I am trying to get each data set to have identical formatting so I can stack them into one data set. I have tried this code from the SAS website: 

data new;
   orig = '12345678';
   new = input(orig, 8.);
   drop orig;
   rename new=orig;
run;

but it has not seemed to work. 

 

Here is what it looks like when I do it.

data testpalmbeach17;
set finalsii.palmbeachfinal17;
exmpt_04 = $1.;
exmpt_04 = input(exmpt_04, best12.);
drop exmpt_04;
rename exmpt_04=exmpt_04;
run;

Here is the log:

 

351  data testpalmbeach17;
352  set finalsii.palmbeachfinal17;
353  exmpt_04 = $1.;
                ---
                386
                200
354  input (exmpt_04, best12.);
                    -
                    22
                    76
ERROR 386-185: Expecting an arithmetic expression.

ERROR 200-322: The symbol is not recognized and will be ignored.

ERROR 22-322: Syntax error, expecting one of the following: a name, arrayname, ), -, :, [, _ALL_,
              _CHARACTER_, _CHAR_, _NUMERIC_, {.

ERROR 76-322: Syntax error, statement will be ignored.

355  drop exmpt_04;
356  rename exmpt_04=exmpt_04;
357  run;

ERROR: No DATALINES or INFILE statement.
WARNING: The variable exmpt_04 in the DROP, KEEP, or RENAME list has never been referenced.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.TESTPALMBEACH17 may be incomplete.  When this step was stopped there
         were 0 observations and 181 variables.
WARNING: Data set WORK.TESTPALMBEACH17 was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           0.20 seconds
      cpu time            0.04 seconds


 

Super User
Posts: 13,300

Re: Matching Formats in order to stack data sets with a set function

Posted in reply to andrewfau

What are you intending with

exmpt_04 = $1.;

You have used syntax that assigns a value to the variable exmpt_04. but the value is not Numeric ($ not acceptable in numeric value) and is not a character value which would be indicated by quotes around the value.

 

If you want to assign a FORMAT then use a format statement: Format exmpt_04 $1. ; would mean that variable is expected to be a character variable and the value will be displayed using one character (even if there are more in the value).

 

with

input (exmpt_04, best12.);

since the INPUT starts the statement SAS expects to read values from either a file represented by an infile statement or lines of data indicated in the code using DATALINES, CARDS or the variants of those statements. You did not quite follow the pattern which has the value assigned first. Referencing a value already in your data would be

 

newvar = input(oldvar,informat.);

 

If you read external files to create your three input data sets then you might be better off to start over and do it so that the variables are the same types to begin with. What you describe is a very common occurrence when relying on Proc Import or an import wizard (which builds proc import code). Proc Import guesses as the correct variable type and length when importing and differences between files will mean the guesses are different, causing the issue you have.

Super User
Posts: 6,628

Re: Matching Formats in order to stack data sets with a set function

Posted in reply to andrewfau

The code from the SAS website is fine.  You adapted it, but tried to re-use the same name for both the character and the numeric version of your variable.  Try it this way:

 

data testpalmbeach17;
set finalsii.palmbeachfinal17;
exmpt_04_new = input(exmpt_04, best12.);
drop exmpt_04;
rename exmpt_04_new=exmpt_04;
run;

Super Contributor
Posts: 266

Re: Matching Formats in order to stack data sets with a set function

Posted in reply to andrewfau

Instead of

data testpalmbeach17;
set finalsii.palmbeachfinal17;
exmpt_04 = $1.;
exmpt_04 = input(exmpt_04, best12.);
drop exmpt_04;
rename exmpt_04=exmpt_04;
run;

...you need to create a new variable with the other type. Like this:

 

data testpalmbeach17;
set finalsii.palmbeachfinal17(rename=exmpt_04=exmpt_04_x);
attrib exmpt_04 length=8;
exmpt_04 = input(exmpt_04_x, best12.);
drop exmpt_04_x;
run;

If I've interpreted your code correctly, exmpt_04 in the existing dataset is character, but you want it to be numeric.

Super User
Posts: 9,880

Re: Matching Formats in order to stack data sets with a set function

Posted in reply to andrewfau

Your code does not look like the code from the SAS website. You need to follow its example line by line in creating a new variable and then dropping the old and renaming the new one.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Esteemed Advisor
Posts: 5,479

Re: Matching Formats in order to stack data sets with a set function

Posted in reply to andrewfau

You were very close. This will work:

 

data testpalmbeach17;
set finalsii.palmbeachfinal17;
exmpt_04_num = input(exmpt_04, best12.);
drop exmpt_04;
rename exmpt_04_num=exmpt_04;
run;
PG
Ask a Question
Discussion stats
  • 5 replies
  • 74 views
  • 0 likes
  • 6 in conversation