BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
seltonsy
Quartz | Level 8

Hi,

 

I have two charact variables that I want to change to numeric.

 

The first has continuous numbers in it, but also has 4 charact. inputs. I would like to keep the continous numbers and change the character to missing.

 

I tried creating new variable with format and the next code but did not work. Also compress method tells me : too many arguments.

 

FORMAT NEW_VAR1   3.;

IF VAR1= 'N/A' THEN NEW_VAR1 = . ;
IF VAR1= 'Unk' THEN NEW_VAR1 = . ;
IF VAR1= 'n/a' THEN NEW_VAR1 = . ;
IF VAR1= 'unk' THEN NEW_VAR1 = . ;
IF VAR1= '' THEN NEW_VAR1 = . ;
ELSE NEW_VAR1 = VAR1;

 

The second variable is smoking variable, it has 5 values as below :


FORMAT  SMOKING  3.;

 

IF CIGARETTE= 'NEVER' THEN SMOKING = 0 ;
IF CIGARETTE= 'BEFORE' THEN SMOKING = 1 ;
IF CIGARETTE= 'NOW' THEN SMOKING = 2 ;
IF CIGARETTE= 'EVERYDAY' THEN SMOKING = 2 ;
IF CIGARETTE= 'UNKNOWN' THEN SMOKING = . ;
IF CIGARETTE= '' THEN SMOKING = . ;

 

The ERROR log :

 

NOTE: Character values have been converted to numeric values at the places given by:
(Line):(Column).
2404:5 2460:6 2461:16 2471:6 2472:19
NOTE: Invalid numeric data, ........

 

Is there something that I'm missing?

 

Thanks,

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

Actually, it did work, but the log showed all of the records where alpha characters were shown. You can do it suppressing those errors with the following:

 

libname thedata '/folders/myfolders';

DATA EXMED_NOTMULTI_3;
  set thedata.exmed_test1 (rename=(distancetestmarche6minfinprogram=_d));
  distancetestmarche6minfinprogram=input(compress(_d,'kd'),?? 12.);
run;

proc freq data=EXMED_NOTMULTI_3 ;
  tables distancetestmarche6minfinprogram;
  where not missing(distancetestmarche6minfinprogram);
run;

Art, CEO, AnalystFinder.com

View solution in original post

29 REPLIES 29
art297
Opal | Level 21

Would help if you provide some example data and what you want as a result (based on that example data).

 

You will likely need to use the input function rather than just have the new numeric variable equal the original character variable, but if there are any instances you haven't accounted for with your if statements, you'll also have to use compress or a similar function to eliminate the characters in the variable.

 

Art, CEO, AnalystFinder.com

 

seltonsy
Quartz | Level 8

Hi,

 

Hi,

 

Thanks for the reply, 

I tried compress before, it worked for another variables but not the ones with 5 character values, it said (error too many arguments)

 

I'm uploading a tata file with the two variables that need to be reformatted.

 

Thanks,

 

 

seltonsy
Quartz | Level 8
The attachement is not working, I uploaded a onedrive link. It's from my university account (secured)
Reeza
Super User

Which lines is the log referring to?

 

You can simplify your code by using IN

 

if var1 in ('N/A', 'Unk', ....etc) then new_Var1=.;

else ...

 

 

seltonsy
Quartz | Level 8

Hi,

 

Thanks for the reply, 

I tried it but it did not work. 

 

I'll upload a data file wit the two variables

seltonsy
Quartz | Level 8
Hi,

Thanks for the reply,
I tried it but it did not work.

I'll upload a data file wit the two variables
Astounding
PROC Star

Most likely, your data contains additional values for your character variables that your programming statements must take into account.  Try running this program to see a list of all of those:

 

proc freq data=have;

tables var1;

where var1 > ' ' and input(var1, ??8.) = .;

run;

 

Unfortunately, you need to run this type of program for each variable separately.  But it will point out what you need to work on.

 

Alternatively, you could just perform the conversion without worrying about which values can't be converted:

 

new_var1 = input(var1, ??8.);

 

The ?? suppresses messages about invalid data when trying to convert from character to numeric.

seltonsy
Quartz | Level 8

Hi,

 

Thanks for the reply, 

I tried it but it did not work. 

 


386 proc freq data=EXMED_NOTMULTI_3;
387 tables TAS3moisvisite1;
388 where TAS3moisvisite1 > ' ' and input(TADdebutvisite1, ??8.) = . ;
-
22
200
ERROR: Erreur de syntaxe détectée lors de l'analyse de la clause WHERE.
ERROR 22-322: Expecting un nom de format.
ERROR 200-322: The symbol is not recognized and will be ignored.
389 run;

 

 

sorry for the french part,

 

 

I'll upload a data file wit the two variables

Astounding
PROC Star

Looks like you changed the variable name.  Use the same variable name throughout the WHERE statement.

seltonsy
Quartz | Level 8
Sorry for that,

I was trying several variables. It gives me the same error message

391 proc freq data=EXMED_NOTMULTI_3;
392 tables TADdebutvisite1;
393 where TADdebutvisite1 > ' ' and input(TADdebutvisite1, ??8.) = . ;
-
22
200
ERROR: Erreur de syntaxe détectée lors de l'analyse de la clause WHERE.
ERROR 22-322: Expecting un nom de format.
ERROR 200-322: The symbol is not recognized and will be ignored.
394 run;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE FREQ used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds


seltonsy
Quartz | Level 8

The attachement is not working, I uploaded a onedrive link. It's from my university account (secured)

Astounding
PROC Star

Guessing at the meaning of the notes in the log ...

 

I would guess that you are using the name a numeric variable in the TABLE and WHERE statements.  You need to be using the character variable that you are trying to turn into a set of numeric values.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 29 replies
  • 1668 views
  • 4 likes
  • 7 in conversation