Help using Base SAS procedures

issue in getting labels when importing csv file using proc import in SAS Unix

Reply
Occasional Contributor
Posts: 7

issue in getting labels when importing csv file using proc import in SAS Unix

I'm using SAS Unix , whenever i try to import a CSV file , using PROC IMPORT, the label of the last column always comes as VAR , even though i use getnames=yes, still the last column does not have label.

Super User
Posts: 10,463

Re: issue in getting labels when importing csv file using proc import in SAS Unix

You likely have a column header that duplicates that of another column or if it is longer than 32 characters then duplicates another long column header for the first 32 characters.

After running proc import look in the log for the generated datastep code and look at the variables. If you have long names you might find some that are truncated. Those are likely candidates.

You can bring the code into the editor by using the F4 key (recall last code) or copy and paste from the log and edit the program to assign a name to the offending variables.

Actually 95% or more of the time I do this with CSV files because at least one variable will come in with either a default data type incorrect, I want a different format than that assigned by Import, or to add meaningful label statements because of similar variable names.

Respected Advisor
Posts: 3,777

Re: issue in getting labels when importing csv file using proc import in SAS Unix

This program demonstrates the duplicate name scenario you suggest.

filename FT15F001 temp;
proc import datafile=FT15F001 out=class dbms=csv replace;
  
getnames=yes;
   parmcards;
Name,Sex,Age,Height,Height
Joyce,F,
11,51.3,50.5
Thomas,M,
11,57.5,85
;;;;
   run;




Occasional Contributor
Posts: 7

Re: issue in getting labels when importing csv file using proc import in SAS Unix

I dont have a duplicate key, I checked the ipunt file.

Respected Advisor
Posts: 3,777

Re: issue in getting labels when importing csv file using proc import in SAS Unix

What do you mean KEY?  I am demonstrating the behavior when there is a duplicate name.

You need to show the data file.

Valued Guide
Posts: 632

Re: issue in getting labels when importing csv file using proc import in SAS Unix

I am actually struggling with a similar problem - extra variables.  The CSV file is generated by MS Access (I believe).  I have checked for duplicate headers and long names.  The TERMSTR= option does not help.  My suspicion was extra trailing commas, but that did not seem to be the case.

The data seem to go where they should and the extra columns (VAR31 and VAR32 in my case) are missing.  Converting to xlsx and then back to CSV (hoping to remove odd characters) does not help.  Before I just DROP VAR:; i would like to discover why - probably just to be sure.  The system generates quite a few of these csv files not all have this issue.

*** update ***

As I continued to work on this I have found a parsing problem in the IMPORT process that generates the DATA step that actually reads the CSV file.  I have not solved this yet, but what I think I know so far is that a long DSD string is incorrectly parsed.  the field in question contains a quoted string and some special characters such as:  "blah blah ""special blah"" other blah + more blah"

The resulting DATA step has incorrect lengths assigned to the INFORMATs and an extra VARxx.  Ultimately the data go into the correct fields, but with the incorrect lengths there is truncation.

I will try to get permission to post the CSV or a portion of it.

Message was edited by: Art Carpenter

Occasional Contributor
Posts: 7

Re: issue in getting labels when importing csv file using proc import in SAS Unix

When i pressed the F4 key, i was able to assign a name, but thsi will becoem tedious if i have more columns. When i was recalling the code, what i saw is teh last column in the csv file is a numeric columnm, but sas is reading it as charater column, but this is happening only with last column always. I tried to check the spaces in the label, nothing was there. The name is just four letters.

Super User
Super User
Posts: 7,392

Re: issue in getting labels when importing csv file using proc import in SAS Unix

Perhaps you could post an example file which causes you this problem?

Super User
Posts: 10,463

Re: issue in getting labels when importing csv file using proc import in SAS Unix

Even posting just the header row, without data will likely help.

If SAS is assigning character to what you think is numeric, one of the rows of data within the value of GuessingRows has something, possibly even a notprintable character, that isn't numeric.

Another reason why I look at the code generated by proc import.

Super User
Posts: 9,662

Re: issue in getting labels when importing csv file using proc import in SAS Unix

Your need TERMSTR= option.

https://communities.sas.com/message/150879#150879

termstr=CRLF


Occasional Contributor
Posts: 7

Re: issue in getting labels when importing csv file using proc import in SAS Unix

https://communities.sas.com/message/19454

There are posts which are already discussed on the similar issue, I'm using SAS hich is installed on Unix machione and the files are created on Windows desktop and then i ftp to my Unix Server, so far whatever i have gone through it looks there is some carriage return or line feed at the end of the column in my csv file, which is causing the issue.

The solutions that re suggested are extending the DLM to use '0D'x or using the dos2unix commands, TERMSTR. Sorry i cannot post the data file.

Occasional Contributor
Posts: 7

Re: issue in getting labels when importing csv file using proc import in SAS Unix

I have tried lot of option, finalley when i googled it out and searched for various blogs, finally found this.

cat file.txt | tr -d '\r' > file_new.txt

it works , type the above command in Unix, then you import the csv file in sas, everything comes fine.

Valued Guide
Posts: 3,208

Re: issue in getting labels when importing csv file using proc import in SAS Unix

If you are convinced the CR LF (0d 0a) is causing your issue. Than that  is normal is the conventions on that part between Windows and Unix are different.

Be happy with SAS you can use the TERMSTR to adjust that. Is a good explantion as 0d LF is typical not numeric but char  even when your eyes will not mark those LF bytes. There are more of them high/low value etc. Computers are doing things as being instructed not necessary the same what humans are expecting .

Hope you will not caught by encoding issue-s as of utf-8 / latin1. Windows is commonly using utf-8(multi byte), SAS often limited to latin1 (single byte).

---->-- ja karman --<-----
Occasional Contributor
Posts: 7

Re: issue in getting labels when importing csv file using proc import in SAS Unix

Thank You all for the replies.

Ask a Question
Discussion stats
  • 13 replies
  • 3273 views
  • 0 likes
  • 7 in conversation