CODEWITHSUNDEEP
stringsasascii
johnny_kb
johnny_kb

Reputation: 714

Iterate through sas column values - string manipulation

My aim is to obtain from a CSV file several numeric columns, called tempi, to go further with other analysis. Unfortunately last column is obtained as text type. I read in SAS documentation that input() function can convert text into a numeric format, so I tried to invoke it but it failed for the first 5 rows, showing on the log window:

NOTE: Invalid argument to function INPUT at line 3278 column 10.

I came along to fix it by examining the hexadecimal code of every element in that column. As I noticed, only the last row (that is the successful row) haven't got a CR character (0D). The other ones got it. I've found compress function that is useful for the scope, so I used it in the data step but it didn't remove the CR character. I show you my code:

data work.meteo;
  drop var7;
  set work.meteo(rename=(var1=Prov var2=temp1 var3=temp2 var4=temp3 var5=temp4));
  x=compress(var6, '\r');
  format x $hex10.;
  temp5 = input(x, 8.);
run;

and my dataset: dataset_weather

I think that compress couldn't be used with a column name, but how could I iterate through column values to invoke it correctly? Is there a better way to reach the same aim? Thanks.

Edited question to Joe's answer: Yes, I tried two ways to read csv source file; the first one consists of a Proc import:

proc import  datafile = 'path-file\...\meteo.csv'
out= work.meteo
dbms = CSV replace;
getnames = no;
run;

the second one is made with infile and termstr. 

data mydata;
  infile "path-file\...\meteo.csv" 
    dsd termstr=crlf truncover;
  input Prov $ temp1 temp2 temp3 temp4 temp5 ;
run;

In the last method, it leads to a dataset with no values in temp5 (except the last row).

Upvotes: 0

Views: 327

Answers (2)

Tom
Tom

Reputation: 51601

Perhaps your problem lines have CR+CR+LF at the end? Or your real end of line is just LF and only the first few lines have the CR.

Try stripping all of the CR's from the line before reading the variables.

data mydata;
  infile "path-file\...\meteo.csv" termstr=LF dsd truncover ;
  input @ ;
  _infile_ = compress(_infile_,'0D'x);
  input Prov $ temp1 temp2 temp3 temp4 temp5 ;
run;

Upvotes: 2

Joe
Joe

Reputation: 63424

I suspect the ultimate answer lies in your CSV input: namely, you probably can modify your input so the carriage return doesn't show up. If you provide some information on the input we can probably help there (namely, how are you importing this - are you using PROC IMPORT I suspect? And why are there 0D characters on the first five rows but not past that?) It's possible TERMSTR or DLM might be able to help here.

But, since you ask this question: you can't use /r the way you do - that's not a meaningful thing to SAS. You instead need compress(x,byte(13)) or alternatively compress(x,'0D'x), both of which work fine.

Upvotes: 1

Related Questions