topic How to control the type of variable with a Proc Import? in SAS Procedures

How to control the type of variable with a Proc Import?

stephaniektaf — Tue, 03 Jul 2018 08:25:39 GMT

Hello everybody,

I am struggling with some imports of Excel files on SAS/UE. I would like to import two files that have a common variable and merge them. The problem is that one is being imported with the variable in character and the other one in numeric which makes the table merging impossible. I tried several techniques after reading posts online: "guessingrows" option, "mixed=YES" option, or even something like:

%let varlist = Code 8 Townname $15;
data myfile;
    length &varlist;
    set myfile;
run;

The code I have been using is quite simple:

    proc import datafile="/folders/myshortcuts/myfile.xlsx"
        out=myfile
        dbms=xlsx
        replace;
    run;

I really don't know what to do at this point. Thanks a lot.

Best regards,

Stephanie

Re: How to control the type of variable with a Proc Import?

RW9 — Tue, 03 Jul 2018 09:05:37 GMT

Excel is a bad format - no structure.

Proc report is a guessing procedure, it guesses what your data is supposed to be.

Add the two together and you will always have issues.

Answer, use a simple data transfer file format - CSV, XML - etc. and write a specific data import program to read the data in accurately as you know the data should be.

So first step, in Excel save as and save the file as a comma separated variable file. This is a text file with data separated by commas.

Now, run your proc import on this csv file - in the log you will see a datastep showing you what the import procedure has guessed for the data. You can copy that code out to your code window, and then modify it to read in the data correctly. E.g. it will look something like:

data want;
  infile "...you_csv_file.csv" dlm=",";
  length abc 8
         def  $12;
  format abc 8
         def $12;
  informat abc 8
           def $12;
  input abc 
        def $;
run;

So this specifies the file to read in, the lengths of each variable, the format to display it as, the format to read the data, and then the read statement. In this way you fully control every aspect of the import.

Re: How to control the type of variable with a Proc Import?

stephaniektaf — Tue, 03 Jul 2018 09:51:27 GMT

Hello @RW9,

Thank you for your reply. I have done what you asked me to do by saving the Excel file as a CSV file (they are automatically separated by a semicolon). I put the same code than you for each length, format, informat and input. As the variables are all characters, I added "$8." (with a full stop at the end because SAS doesn't recognize the format if written "$8"). But it gives me a table separated every 8 characters and not every time it meets a semicolon.

What do you think is the problem?

Thanks a lot,

Stephanie

Re: How to control the type of variable with a Proc Import?

Kurt_Bremser — Tue, 03 Jul 2018 10:04:50 GMT

When you already have an informat statement, then you need not use any informats in the input statement. Even the $ for character variables is not necessary if you assigned a length or an informat.

Take care to have a dlm=';' in the infile statement.

For further help, post your code and a few example lines if the infile, using the proper buttons ({i} and "little running man").

Re: How to control the type of variable with a Proc Import?

RW9 — Tue, 03 Jul 2018 10:20:02 GMT

What is posted was an example unrelated to your data.

Please refer to the statement:

"Now, run your proc import on this csv file - in the log you will see a datastep showing you what the import procedure has guessed for the data. You can copy that code out to your code window, and then modify it to read in the data correctly. E.g. it will look something like:"

As I cannot see your data, nor program or anything else, I cannot write anything specifically for you, only suggest some general things.

Also note that a semicolon delimited file is not a CSV file. You will need to change the delimiter to:

infile "...you_csv_file.txt" dlm=";";

And I would encourage you not to name delimited files as CSV, hence I changed my example extension to be .txt.

Re: How to control the type of variable with a Proc Import?

Tom — Tue, 03 Jul 2018 11:46:47 GMT

You normally want to add the DSD option to the INFILE statement when reading delimited files. This will treat adjacent delimiters as meaning there is an empty field. Otherwise multiple adjacent delimiters are treated as one delimiter.

Re: How to control the type of variable with a Proc Import?

stephaniektaf — Thu, 05 Jul 2018 10:34:23 GMT

Hi @Kurt_Bremser,

Thanks for your reply. It actually worked. However, the csv file is unable to take into account foreign language compared to the proc import. I changed the delimiter as ";" in the infile statement as mentionned.

data Observation;
  infile "/folders/myshortcuts/Observations/file.csv" dlm=";";
  length Code $8. Townname $20. State_Code $20. State_Name $20. State_M_Name $20. Township_Code $20. Township_Name $20. Township_M_Name $20.;
  format Code $8. Townname $20. State_Code $20. State_Name $20. State_M_Name $20. Township_Code $20. Township_Name $20. Township_M_Name $20.;
  informat Code $8. Townname $20. State_Code $20. State_Name $20. State_M_Name $20. Township_Code $20. Township_Name $20. Township_M_Name $20.;
  input Code Townname State_Code State_Name State_M_Name Township_Code Township_Name Township_M_Name;
run;

Thanks a lot,

Best regards,

Stephanie

Re: How to control the type of variable with a Proc Import?

RW9 — Thu, 05 Jul 2018 10:38:54 GMT

Add encoding= option:

http://support.sas.com/documentation/cdl/en/nlsref/63072/HTML/default/viewer.htm#n0utx4w7x4exijn1wt7pk0srrxz5.htm

Re: How to control the type of variable with a Proc Import?

stephaniektaf — Thu, 05 Jul 2018 10:43:18 GMT

Hi @RW9,

Yes that was an example. I wrote the code:

data Observation;
  infile "/folders/myshortcuts/Observations/file.csv" dlm=";";
  length Code $8. Townname $20. State_Code $20. State_Name $20. State_M_Name $20. Township_Code $20. Township_Name $20. Township_M_Name $20.;
  format Code $8. Townname $20. State_Code $20. State_Name $20. State_M_Name $20. Township_Code $20. Township_Name $20. Township_M_Name $20.;
  informat Code $8. Townname $20. State_Code $20. State_Name $20. State_M_Name $20. Township_Code $20. Township_Name $20. Township_M_Name $20.;
  input Code Townname State_Code State_Name State_M_Name Township_Code Township_Name Township_M_Name;
run;

And it worked. However, I did not understand the technique cited on running a proc import on the csv file. I think it is because it uses the delimiter "," and not ";". I have a csv file with semicolon as delimiter maybe because the laptop is french. The log gives me:

 86             data WORK._OBSERVATION2    ;
 87             %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
 88             infile '/folders/myshortcuts/Observations/file.csv' delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;
 89                informat Code_Townname_State_Code_State $76. ;
 90                format Code_Townname_State_Code_State $76. ;
 91             input
 92                         Code_Townname_State_Code_State  $
 93             ;
 94             if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
 95             run;

Also, it is the wrong informat, format and input. That is maybe why I have a weird file with the proc import.

Thanks a lot,

Stephanie

Re: How to control the type of variable with a Proc Import?

Kurt_Bremser — Thu, 05 Jul 2018 10:48:50 GMT

From the variable names I guess that there's no really confidential information in that file, so it would really help a lot if you copy/pasted the first 10 lines or so into a separate file and attached that to your next post here, so we have something to play around with.

Re: How to control the type of variable with a Proc Import?

stephaniektaf — Thu, 05 Jul 2018 10:54:42 GMT

Ok I just added it. Thanks a lot.

data Observation;
  infile "/folders/myshortcuts/Observations/file.csv" dlm=";" dsd;
  length Code $8. Townname $20. State_Code $20. State_Name $20. State_M_Name $20. Township_Code $20. Township_Name $20. Township_M_Name $20.;
  format Code $8. Townname $20. State_Code $20. State_Name $20. State_M_Name $20. Township_Code $20. Township_Name $20. Township_M_Name $20.;
  informat Code $8. Townname $20. State_Code $20. State_Name $20. State_M_Name $20. Township_Code $20. Township_Name $20. Township_M_Name $20.;
  input Code Townname State_Code State_Name State_M_Name Township_Code Township_Name Township_M_Name;
run;

But it seemed to work already properly without the dsd.

Best regards,

Stephanie

Re: How to control the type of variable with a Proc Import?

Tom — Thu, 05 Jul 2018 12:18:04 GMT

@stephaniektaf wrote:

Ok I just added it. Thanks a lot.

data Observation;
  infile "/folders/myshortcuts/Observations/file.csv" dlm=";" dsd;
  length Code $8. Townname $20. State_Code $20. State_Name $20. State_M_Name $20. Township_Code $20. Township_Name $20. Township_M_Name $20.;
  format Code $8. Townname $20. State_Code $20. State_Name $20. State_M_Name $20. Township_Code $20. Township_Name $20. Township_M_Name $20.;
  informat Code $8. Townname $20. State_Code $20. State_Name $20. State_M_Name $20. Township_Code $20. Township_Name $20. Township_M_Name $20.;
  input Code Townname State_Code State_Name State_M_Name Township_Code Township_Name Township_M_Name;
run;

But it seemed to work already properly without the dsd.

Best regards,

Stephanie

DSD option will allow it to handle missing values properly. You probably did not have any missing values.

You should also add TRUNCOVER option to prevent SAS from going to the next row if any of the lines have too few values.

Why do you have periods after the numbers in the LENGTH statement? Those are not formats, they are lengths.

Why are you attaching $xx formats and informats? SAS already knows how to read and write character variables, so there is no need to attach special formats or informats for them. The same is true for most numbers. Although you might want to attach formats to floating point numbers to specify how many decimal places it should print by default.

Re: How to control the type of variable with a Proc Import?

Tom — Thu, 05 Jul 2018 12:14:05 GMT

Looks like you told PROC IMPORT that the delimiter was a comma instead of a semi-colon. You can change the options on the PROC IMPORT step.

proc import datafile="/folders/myshortcuts/Observations/file.csv"
  data=Observation replace
  dbms=csv 
; 
  delimiter=';';
run;