topic Importing several csv files into SAS in SAS Programming

Importing several csv files into SAS

almmotamedi — Sat, 06 Feb 2016 00:13:53 GMT

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,

Re: Importing several csv files into SAS

Reeza — Sat, 06 Feb 2016 01:22:34 GMT

Did you manage to get your files uploaded to the server or somewhere that you can use data step imports rather than the GUI?

Re: Importing several csv files into SAS

almmotamedi — Sat, 06 Feb 2016 01:35:42 GMT

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

Re: Importing several csv files into SAS

Reeza — Sat, 06 Feb 2016 02:12:03 GMT

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.

Re: Importing several csv files into SAS

almmotamedi — Sat, 06 Feb 2016 02:21:21 GMT

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

Re: Importing several csv files into SAS

Reeza — Sat, 06 Feb 2016 02:36:25 GMT

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-text-files-that-have/ta-p/223627

Re: Importing several csv files into SAS

almmotamedi — Sat, 06 Feb 2016 19:26:28 GMT

Reeza, my previous thread did not get an appropriate answer. Thats why I created a new question thread.

Re: Importing several csv files into SAS

Cynthia_sas — Sun, 07 Feb 2016 19:13:39 GMT

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

Re: Importing several csv files into SAS

almmotamedi — Mon, 08 Feb 2016 14:40:19 GMT

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

Re: Importing several csv files into SAS

Reeza — Mon, 08 Feb 2016 15:24:39 GMT

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 | $

Re: Importing several csv files into SAS

ballardw — Mon, 08 Feb 2016 17:06:50 GMT

Additonal import bits of information for use to know:

1) Do ALL of the files you want to read have the same structure (number of columns, variables that mean the same thing in the same column in each file).

2) Do any or all of your files have column headers?

3) are you trying to read multiple files into a single data set?

Re: Importing several csv files into SAS

SEC — Mon, 08 Feb 2016 20:27:20 GMT

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;

Re: Importing several csv files into SAS

almmotamedi — Mon, 08 Feb 2016 20:58:48 GMT

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

Re: Importing several csv files into SAS

SEC — Mon, 08 Feb 2016 21:08:47 GMT

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).

Re: Importing several csv files into SAS

almmotamedi — Mon, 08 Feb 2016 21:22:37 GMT

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

Re: Importing several csv files into SAS

SEC — Mon, 08 Feb 2016 22:28:09 GMT

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

Re: Importing several csv files into SAS

almmotamedi — Tue, 09 Feb 2016 14:46:39 GMT

Yes, I can change the header rname to whatever I want, but the column will appear as an empty column!

Also, I get "Variable is not an object." error when I run the code.

Thank you for help

Re: Importing several csv files into SAS

almmotamedi — Tue, 09 Feb 2016 16:51:35 GMT

Here is my wildcard code:

data details_FEB;
infile "/v_share/Xiaomin/SCOPES/sas_dataset/Oct-08-2015/ScopesCalculations_SecondaryConstruction_*" linesize = 300 firstobs = 2;

input
C_ST $3.0
C_DSTRB_AREA $5.0
N_RSK $5.0

IsRevision9 $1.0
ScheduleNumberException $2.0
IsAsgrReportExists $2.0

ConstructionClass 1.0
COT00503.C_CSP_CONST_CLS 1.0

ERROR: DATA STEP Component Object failure. Aborted during the COMPILATION phase.
ERROR 557-185: Variable COT00503 is not an object.
COT00503.F_CSP_CONST_CLS 1.0
CombustibilityClass 1.0
COT00503.C_COMB_CLS 1.0
COT00503.F_COMB_CLS 1.0
COT00503.N_STOR 1.0
PartialSprinklerCreditFactor 1.0
LimitedSupplyCreditFactor 1.0
COT00503.C_SPK $1.0
SprinklerCreditType $1.0
COT00512.C_IPM $1.0
InternalProtectionMethod $1.0
TotalFloorArea 6.0
COT00504.R_TL_FLR_AREA 6.0
TotalRoofArea 6.0
COT00504.R_TL_RF_AREA 6.0
TotalSpecificFloorArea 6.0
COT00504.R_TL_SPEC_FLR 6.0
TotalFloorAndRoofArea 6.0
COT00520.C_SEC_CONST $1.0
COT00511.N_BGN_LVL 1.0
COT00511.N_THRU_LVL 1.0
COT00511.C_VO_PROT $1.0
COT00515.N_SCHD 15.0
FloorLevel 1.0
COT00516.N_LEN*COT00516.N_WDTH 6.0
COT00516.C_LVL_TYP $10.0
IsLowestFloor $10.0
COT00511.F_DIV_WL $10.0
COT00516.F_OTH $10.0
COT00516.C_AS_TYP $10.0
COT00520.N_SQFT $10.0
COT00520.C_RF_SRFC $10.0
COT00520.F_COMB_SB $10.0
COT00520.F_FOAM_PLSTC $10.0
COT00520.F_ACPT_TB $10.0
COT00520.R_PCT_STOR_AFCT $10.0
COT00520.R_PCT_OTH_FLR 3.0
COT00520.C_BLDG_COND $5.0
Percentage $5.0
COT00520.R_PCT_OTH_FLR $5.0
EffectiveArea $5.0
COT00520.R_TL_FLR_AREA $5.0
Charge $5.0
COT00520.R_CHRG $5.0
COT00520.F_CHRG $5.0
SecondaryConstructionCharge $5.0
COT00520.R_TL_SEC_CONST $5.0;
run;

Could you please help?

Re: Importing several csv files into SAS

Reeza — Tue, 09 Feb 2016 16:53:42 GMT

You've marked this question answered, and it's gotten too convoluted, so I think it's time to start a new thread. Forget about reading in multiple files for the moment, get one file read in successfully first and them move forward. If you can attach a sample of your file with your code in the new question, that will help. Please be clear in your new question and don't assume any prior knowledge of the problem.

Re: Importing several csv files into SAS

almmotamedi — Tue, 09 Feb 2016 16:59:49 GMT

Sure, I think my best bet is using wildcard (I created a new thread for that). I appreciate if you take a look at that. The code works for some INPUT variables but not for all of them!

I am so frustrated!!