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

Dear all,

 

I have data in a txt file (see attached).

 

First, I read them to SAS:

 

data have;
	infile 'mypath\sample.txt'  dlm='09'x dsd lrecl=4096 truncover firstobs=2 termstr=LF;
	input Mark Name	:$20. Country :$20.	Type :$20. Id  var1 :$20.	var2 :$20.	var3 :$20.	var4 :$20.	var5 :$20.
		var6 :$20.	var7 :$20.	var8 :$20.	var9 :$20.	var10 :$20.	var11 :$20.;
run;

Now, I want to convert var11 to numeric:

 

data want;
	set have;
	new=compress(translate(var11,"", '"n.a.", ')); /* remove all unnecessary characters*/
	new1=input(new, 12.); /* convert it to numeric*/
	new2=new*1; /* alternative way*/
	keep var11 new:;
run;

The problem is that it does not convert the variable. However, when I use var2 instead of var11, everything is fine

 

data want1;
	set have;
	new=compress(translate(var2,"", '"n.a.", ')); /* remove all unnecessary characters, here I use var2*/
	new1=input(new, 12.); /* convert it to numeric*/
	new2=new*1; /* alternative way*/
	keep var2 new:;
run;

Why converting var11 does not work?

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
Maybe you have other non-digit characters.

data want;
	set have;
	new=compress(var11, , 'kd')); /* remove all unnecessary characters*/
	new1=input(new, 12.); /* convert it to numeric*/
.............................

View solution in original post

5 REPLIES 5
ballardw
Super User

Once a variable is created is has a data type, numeric of character and will not change.

 

If I am reading a file then I would make sure that the resultant data type when first read is the desired type. instead  of Var11 :$20.

try Var11 : Best20.;

 

Other wise one of the many-times-repeated-approaches on this forum is:

 

data want;

   set have (rename=(var11=oldvar11);

   var11 = input(oldvar11,best12.);

   drop oldvar11;

run;

chris2377
Quartz | Level 8

@ballardw Thank you for the answer. I would prefer to read the variable as numeric in the first place, but the problem is that there are 'n.a.'s throughout the file, so when I specify the numeric informat I get all missing values. Is there any way to directly read such variables as numeric?

 

@Ksharp Thank you for the answer. With the translate function I get rid of a thousand separator. Decimals points are separated with a dot in my file. 

PGStats
Opal | Level 21

You should revise the documentation of the translate function. Your use will get rid of decimal points.  

You can either read the var11 field with the proper informat, as suggested by @ballardw or do

 

data want;
	set have(rename=(var11=strVar11));
	var11 = input(strVar11, ?? 20.); /* convert to numeric*/
	drop str:;
run;

Note that 20. and BEST20. are alias informat names.

PG
Ksharp
Super User
Maybe you have other non-digit characters.

data want;
	set have;
	new=compress(var11, , 'kd')); /* remove all unnecessary characters*/
	new1=input(new, 12.); /* convert it to numeric*/
.............................

chris2377
Quartz | Level 8

Perfect. It worked. I wasn't aware of this extra arguments of the compress function. Thanks

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
  • 5 replies
  • 49712 views
  • 1 like
  • 4 in conversation