DATA Step, Macro, Functions and more

Importing several csv files into SAS

Accepted Solution Solved
Reply
Contributor
Posts: 72
Accepted Solution

Importing several csv files into SAS

Could you please give me some advice on how to import tens of csv files into my SAS database at once?, it seems that there are different ways to do it, however, I am not sure which way is more straightforward as I am pretty new into SAS. 

 

I already tried DATA statement, using INPUT and Infile commands, but it gives me error.

 

Thank you for help,


Accepted Solutions
Solution
‎02-08-2016 04:18 PM
Occasional Contributor SEC
Occasional Contributor
Posts: 6

Re: Importing several csv files into SAS

Posted in reply to almmotamedi

If all your files are named similarly with the same layout/fields, then you can use the * wildcard.

This creates a dataset called details_FEB, that reads in all files that start with JR_201602.  Below shows fixed width files, so you will need to modify your input for csv files.

 

Hope that helps.

Sharon

 

 

data details_FEB;

infile "&data_folder./JR_201602*" linesize = 300 firstobs = 2;

input

@1 A $1.0

@2 B $5.0

@18 card_number 8.0

@26 D $19.0

@45 E $6.0

@51 F $5.0

@56 store_redeem 10.0

@66 H $2.0

@68 I $2.0

@70 J $2.0

@72 type $1.0

@73 clerk_id $6.0

 ;

run;

 

View solution in original post


All Replies
Super User
Posts: 19,815

Re: Importing several csv files into SAS

Posted in reply to almmotamedi
Did you manage to get your files uploaded to the server or somewhere that you can use data step imports rather than the GUI?
Contributor
Posts: 72

Re: Importing several csv files into SAS

Yes I did. But I still get an error, and i think there is something wrong with my DATA statement.

i appreciate if you help with it
Super User
Posts: 19,815

Re: Importing several csv files into SAS

Posted in reply to almmotamedi

Post a sample of your file, if you need help with the import code.

 

Also, some key things to answer. 

1. Get your code working for a single file first, then move towards automation. If you're receiving errors please post the log and code. Saying something doesn't work isn't informative and we can't help except ask for more details. Try using a generic proc import code, or the GUI to start off and then you can use the code to springboard an automation process.

2. How are you identifying the files, are they all .csv in a single folder, or do you have a list or some naming convention?

3. Do all the files have the same structure - very important.

 

 

 

 

Contributor
Posts: 72

Re: Importing several csv files into SAS

I already posted the code in my previous posts. The files are all csv files in a same folder with a same struture. Thanks

 

Super User
Posts: 19,815

Re: Importing several csv files into SAS

Posted in reply to almmotamedi

Since you posted a new thread I'm assuming you had a valid reason for separating the threads. It's nice to keep all information in one thread to assist future users with the same question. If your previous question is answered, please mark it as such.

 

Here's a previous answer to this question. 

 

https://communities.sas.com/t5/SAS-Communities-Library/How-do-I-write-a-macro-to-import-multiple-tex...

Contributor
Posts: 72

Re: Importing several csv files into SAS

Reeza, my previous thread did not get an appropriate answer. Thats why I created a new question thread.
SAS Super FREQ
Posts: 8,866

Re: Importing several csv files into SAS

Posted in reply to almmotamedi
But in your previous thread, you only showed the INFILE statement, and Reeza suggested (correctly) that you will not actually read any data without having an INPUT statement. So, you need to post your COMPLETE code here, with an INPUT statement.

For example, imagine that you have gotten a CSV file with these 4 data lines and the file is stored someplace on the server as spendlimit.csv.

The CSV file looks like this:
alan,35,100
barb,33,75
carl,36,80
dana,36,100

The code to read the file into a SAS dataset looks like this:
data new;
infile 'path-to-file/spendlimit.csv' dlm=',' dsd;
input name $ age spend;
run;

proc contents data=work.new;
run;

proc print data=work.new;
run;

cynthia
Contributor
Posts: 72

Re: Importing several csv files into SAS

Posted in reply to Cynthia_sas

I had completed my code by adding INPUT after that.

 

Here is my code again:

 

filename two ("/v_share/Xiaomin/SCOPES/sas_dataset/Oct-08-2015/ScopesCalculations_SecondaryConstruction_ 01_10092015_1158.csv"
"/v_share/Xiaomin/SCOPES/sas_dataset/Oct-08-2015/ScopesCalculations_ScopesCalculations_SecondaryConstruction_ 02_10092015_1219.csv");

data test;
infile two;


INPUT C_ST C_DSTRB_AREA $ N_RSK $ COT00503.D_SRVY $ IsRevision9 ScheduleNumberException IsAsgrReportExists $ COT00503.D_SCHD_APLY $ ConstructionClass COT00503.C_CSP_CONST_CLS COT00503.F_CSP_CONST_CLS CombustibilityClass COT00503.C_COMB_CLS COT00503.F_COMB_CLS COT00503.N_STOR PartialSprinklerCreditFactor LimitedSupplyCreditFactor COT00503.C_SPK $ SprinklerCreditType $ COT00512.C_IPM $ InternalProtectionMethod $ TotalFloorArea COT00504.R_TL_FLR_AREA TotalRoofArea COT00504.R_TL_RF_AREA TotalSpecificFloorArea COT00504.R_TL_SPEC_FLR TotalFloorAndRoofArea COT00520.C_SEC_CONST $ COT00511.N_BGN_LVL COT00511.N_THRU_LVL COT00511.C_VO_PROT $ COT00515.N_SCHD FloorLevel COT00516.N_LEN*COT00516.N_WDTH COT00516.C_LVL_TYP $ IsLowestFloor $ COT00511.F_DIV_WL COT00516.F_OTH COT00516.C_AS_TYP $ COT00520.N_SQFT $ COT00520.C_RF_SRFC COT00520.F_COMB_SB,COT00520.F_FOAM_PLSTC,COT00520.F_ACPT_TB COT00520.R_PCT_STOR_AFCT COT00520.R_PCT_OTH_FLR COT00520.C_BLDG_COND | $ Percentage,COT00520.R_PCT_OTH_FLR EffectiveArea COT00520.R_TL_FLR_AREA Charge COT00520.R_CHRG COT00520.F_CHRG SecondaryConstructionCharge COT00520.R_TL_SEC_CONST;

 

run;

 

I am not sure what address I should refer to for "Infile" statement (as you mentioned). Thank you

Super User
Posts: 19,815

Re: Importing several csv files into SAS

Posted in reply to almmotamedi

I'm assuming this is generating errors? Your input statement should only be the list of variables in the CSV, using valid SAS names. The following aren't valid SAS names. You should double check what your file has, versus what you expect. 

 

 

COT00503.D_SRVY

COT00516.N_LEN*COT00516.N_WDTH

COT00520.C_BLDG_COND | $

 

 

Solution
‎02-08-2016 04:18 PM
Occasional Contributor SEC
Occasional Contributor
Posts: 6

Re: Importing several csv files into SAS

Posted in reply to almmotamedi

If all your files are named similarly with the same layout/fields, then you can use the * wildcard.

This creates a dataset called details_FEB, that reads in all files that start with JR_201602.  Below shows fixed width files, so you will need to modify your input for csv files.

 

Hope that helps.

Sharon

 

 

data details_FEB;

infile "&data_folder./JR_201602*" linesize = 300 firstobs = 2;

input

@1 A $1.0

@2 B $5.0

@18 card_number 8.0

@26 D $19.0

@45 E $6.0

@51 F $5.0

@56 store_redeem 10.0

@66 H $2.0

@68 I $2.0

@70 J $2.0

@72 type $1.0

@73 clerk_id $6.0

 ;

run;

 

Contributor
Posts: 72

Re: Importing several csv files into SAS

Great, thank you. One quick question about the example INPUT statement you mentioned; do I need to specify any value? or calling the variable (column header) is enough?

 

for example, in this line,   @45 E $6.0 , what is exactly the $6.0 ?

 

My files all have the same layout and header, but they have obviously different values and row numbers

 

Thank you

Occasional Contributor SEC
Occasional Contributor
Posts: 6

Re: Importing several csv files into SAS

Posted in reply to almmotamedi

 

Depends on the type of values and lengths, though it is a good idea to tell SAS what type of value you are reading and the length or format.

 

$6.0 tells SAS that the values are characters with the length of 6.

3.0 tells SAS that the values are numeric with the length of 3.

 

It will make it easier to do calculations if you read the data in correctly and you will not truncate long character fields if you specify the length up front.

 

You will probably want to skip the header row (not read it in) because it creates a conflict as the headers are usually characters and the data may be numeric (with different lengths, too).

 

Contributor
Posts: 72

Re: Importing several csv files into SAS

Thank you so much. All of my variables are either numeric or character, EXCEPT one variable :

 

its name is "COT00503.D_SRVY" and its a "date" 

 

Do you think that basically there is a problem with its name? (because of dot)

 

And, how do I specify its FORMAT in INPUT statement?

 

Thank you

Occasional Contributor SEC
Occasional Contributor
Posts: 6

Re: Importing several csv files into SAS

Posted in reply to almmotamedi

Are you saying COT00503.D_SRVY is the header name?  You don't need to use the exact header/field names (and as I mentioned earlier, I wouldn't even bring in that row).  You can change that to "survey_date" for example.  (You can change any or all of the field names to something easier or more descriptive for you, if you want.)

 

There are many, many "date" formats (you can search the SAS site for them), but as an example, if your dates look like this: 03/28/2016

then you would want to read it in as:

 

survey_date    mmddyy10.0

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 19 replies
  • 409 views
  • 0 likes
  • 5 in conversation