BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
almmotamedi
Obsidian | Level 7

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,

1 ACCEPTED SOLUTION

Accepted Solutions
SEC
Fluorite | Level 6 SEC
Fluorite | Level 6

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

19 REPLIES 19
Reeza
Super User
Did you manage to get your files uploaded to the server or somewhere that you can use data step imports rather than the GUI?
almmotamedi
Obsidian | Level 7
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
Reeza
Super User

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.

 

 

 

 

almmotamedi
Obsidian | Level 7

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

 

Reeza
Super User

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

almmotamedi
Obsidian | Level 7
Reeza, my previous thread did not get an appropriate answer. Thats why I created a new question thread.
Cynthia_sas
SAS Super FREQ
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
almmotamedi
Obsidian | Level 7

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

Reeza
Super User

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

 

 

SEC
Fluorite | Level 6 SEC
Fluorite | Level 6

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;

 

almmotamedi
Obsidian | Level 7

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

SEC
Fluorite | Level 6 SEC
Fluorite | Level 6

 

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

 

almmotamedi
Obsidian | Level 7

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

SEC
Fluorite | Level 6 SEC
Fluorite | Level 6

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

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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