topic Re: how to specify some variable format during a proc import data csv file in SAS Enterprise Guide

how to specify some variable format during a proc import data csv file

alepage — Mon, 05 Aug 2024 17:13:29 GMT

Good Afternoon,

Is there a way to specify / modify a variable format with loosing information during a proc import.

One of the variable is map as a numerical variable while it should stay as a string.

%let csvinputpath=/path1/data;
 
Filename mrcsvfil pipe "ls -Art &csvinputpath./*.csv | tail -n 1 ";
 
DATA folderinfo;
Infile mrcsvfil truncover;

Input 	infile_name $100.;
Filename=scan(infile_name,-1,"/","b");
call symput ('csvFile',strip(Filename));
call symput ('cn_csvFile1',strip(infile_name));
RUN;

libname dest1 "path2/temp/";

options validvarname=v7;
PROC IMPORT OUT=dest1.dataretentionpolicieslist
    DATAFILE="&csvinputpath/&csvFile."
    DBMS=CSV
    REPLACE;
	delimiter='|';
    getnames=yes;
    	
RUN;

Re: how to specify some variable format during a proc import data csv file

Tom — Mon, 05 Aug 2024 17:34:40 GMT

If you know the variables do NOT use PROC IMPORT. That is a tool for GUESSING how to read a CSV file. When you know what is in the file just READ it instead.

If you have no idea what is in the files then you could use PROC IMPORT to get a rough idea. Then copy the code it generates and adapt it and rerun it.

If you do use PROC IMPORT then make sure to add the GUESSINGROWS=MAX statement so it does not make the decisions on what types of variables to create without looking at all of the data.

You could also just use a different tool to make the GUESSes about how to read the CSV file. Such as this one: https://github.com/sasutils/macros/blob/master/csv2ds.sas

Which does include a method for you to override its decisions about how to read the variables.

Note that FORMAT has a special meaning in SAS that is different from the way you used it. In SAS a format is instructions for how to display the values. You are asking about how to change the TYPE that PROC IMPORT defines for the the variable, not the format attached to it. Changing the format attached to a variable after it is already created is easy. But changing the type is harder (and might not work since reading a string as a number loses some of the information).

Re: how to specify some variable format during a proc import data csv file

alepage — Mon, 05 Aug 2024 18:39:56 GMT

I have tried the tool import data in SAS EG and I am getting this error:

Une exception de type 'System.OutOfMemoryException' a été levée.

Re: how to specify some variable format during a proc import data csv file

Tom — Mon, 05 Aug 2024 20:11:49 GMT

Sounds like the Enterprise Guide tool is trying to analyze the file and having trouble.

Personally I would just use the copy file task in EG to move the CSV file to the SAS server (not needed if you are connecting EG to PC SAS running on the same PC as EG) and then just use SAS to read the CSV file.