topic Re: Reading multiple files in SAS Programming

Reading multiple files

Aman4SAS — Thu, 01 May 2014 10:54:22 GMT

Hi friends,

I normally read multiple files located in single folder easily.

But this time script is running fine but i m not getting resultant observation.

I would like to call your help on the same.

Required files are attached.

Re: Reading multiple files

RW9 — Thu, 01 May 2014 12:23:06 GMT

Hi,

Have a look at your log, and the intermediary datasets in the process (i.e. run one step at a time) to identify the issue. If you are still having issues then post the output from the log where it shows errors/warnings, or the exact code and some test input where it is not doing what you want. You need to clarify what "resultant observation" you are not getting.

Re: Reading multiple files

Aman4SAS — Thu, 01 May 2014 12:38:27 GMT

First of all thanks for your response.

I have attached folder in zip where files are located and script along with my question.

Its not showing any error its just gicing 0 oberservation.

input and infile are correct , i have checked that to read individual file.

Please suggest.

Re: Reading multiple files

jwillis — Thu, 01 May 2014 13:46:13 GMT

Hi Aman4SAS.

Intriguing question. You're coding style is not like mine so I am reading your code to learn a style that is different than my own. You have at least 3 implied "If this condition is true, continue, else return to the next row". My suggestion is to fully expand these statements to "If *** then do; else ***; end:" statements and place "Put _all_" statements in strategic locations to find out which of the three "continue this data steps processing" is causing nothing to be written. My first observation is that you do not have any data that does meet all three conditions. Also, SAS has "rules" for handling the reading of multiple datasets; and the two datasets are not the same size; and the end of file condition is met on one of the files sooner than you expect. At this point, if this were my problem, and I knew I had data that should be output, I would be going to ' "Combining SAS Data Sets: Basic Concepts" in Chapter 21 of SAS Language Reference: Concepts'. The quoted portion is from the description of the SET statement in the Dictionary of SAS Statements in the book 'SAS 9.3 Statements Reference'. I am reading the books because I am intrigued and fascinated by your style and I want to learn more about it..

if LocationType="M";

if SOC in (113021
119041
119111
119121
151111
151121
151122
151131
151132
151133
151134
151141
);

if SOC NE 0 and SOC/1000 NE round(SOC/1000,1);

Re: Reading multiple files

Aman4SAS — Thu, 01 May 2014 14:13:04 GMT

I would like to request to all stop guessing.

If anyone would be able to read atleast single observation. let me know so i can improve my code.

Thanks to all.

Re: Reading multiple files

ballardw — Thu, 01 May 2014 15:49:33 GMT

When you use the path ..\temp it's kind of hard to diagnose as that is relative to current active directory. I would recommend explicit paths.

I'm not going to guess what your directory structure may be.

Is your dirlist dataset populating correctly?

Re: Reading multiple files

MikeZdeb — Thu, 01 May 2014 16:02:41 GMT

hi ... attached WORKS1.SAS works, modifying your code just a bit (plus I just put the data in folder Z:\ on my PC) mainly those subsetting IF statements in the loop ... and changed CODELIST to a macro variable ...

%let codelist = %str(113021 119041 119111 119121 151111 151121 151122 151131 151132 151133 151134 151141);

then in data step (you need the extra INPUTs to release unused records since you are not returning to the top of the data step while reading each file)...

input

Year

Month

LocationType $ @;

if locationtype ne "M" then do; input; continue; end;

input

Location $

SOC $ @;

if soc ^in (&codelist) then do; input; continue; end;

input

EducationCode

Volume

VolumeNew

;

if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */

if SOC NE 0 and SOC/1000 NE round(SOC/1000,1) then output;

another suggestion (WORKS2.SAS) ... in the data step, you could look at LOCATIONTYPE and SOC first as shown (though given how fast INPUT works I'm not sure why you seem, so concerned with reading the raw data in stages ... seems like overkill to me ... if you are that concerned why leave that last check of SOC to the very end, after you've read all the variables)

also, since you have INFORMAT statements, you don't need the informats in the INPUT statement (yes/no?) ... and just curious, why not just one FORMAT and one INFORMAT statement each with multiple variables ... and, why bother with the numeric informats when using LIST input (not needed, yes/no?)

input @;

if scan(_infile_,3) eq : "M" and input(scan(_infile_,5),best.) in : (&codelist) then do;

input

Year

Month

LocationType

Location

SOC

EducationCode

Volume

VolumeNew

;

if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */

if SOC NE 0 and SOC/1000 NE round(SOC/1000,1) then output; /* Exclude summary records */

end;

else input;

end;

Re: Reading multiple files

Tom — Thu, 01 May 2014 17:46:46 GMT

You are trying to read all of the records from one CSV file by using a DO loop during one iteration of the DATA step.

But inside the DO loop you have coded subsetting IF statements that will cause the whole DATA step to re-start.

So once any of those subsetting IF statements are false you will stop processing the current CSV file and move onto the next one.

Re: Reading multiple files

Aman4SAS — Fri, 02 May 2014 04:56:38 GMT

Hi TOM,

Thanks for your input.

But as mentioned above in that case it must read atleast one observation.

but its showing zero observation it seems its not getting inside of csv files.

Thanks

Re: Reading multiple files

jakarman — Fri, 02 May 2014 05:11:28 GMT

Aman4SAS, Tom is right
Showing the number of obs Observations is telling something on the output dataset.
When there is nothing to output it will tell you 0.... That doesn't say you anything on what has been readed/processed.

The best approach for debugging is eliminating parts of the code (eg %codelist) and see what is happening.

An other remark: There is no need for that step creating a dirlist, you could define the wildcarding immediate at the filepath statement

Re: Reading multiple files

Aman4SAS — Fri, 02 May 2014 05:23:12 GMT

Hi Jaap,

I heartily agree that Tom Sir is right.

"those subsetting IF statements are false you will stop processing the current CSV file and move onto the next one"

at the time moving to next , it must left something behind.

what i can see, its reading all desired observation but its not putting on Created dataset.

The same program is running very fine for for txt files but its not working in my csv files. so i m confuse to remove my subset conditions,

As per using wildcard. Thanks for your valuable input. I have used that to resolved my problem for temp basis. and it works

Thanks

Re: Reading multiple files

jakarman — Fri, 02 May 2014 06:11:12 GMT

Aman4SAS, There was a time computer-resources where that expensive you did a lot of desktopwork (paper pencil) preventing mistakes as much as possible. The hollerith cards very patient doing some coding with that. At his era your time is much more valuable than the one of the machine. As long as your causing more trouble (losing data, changing/sending data) a try to verify some assumption is more effective.

Put a * (comment or remove) that %codelist (drop that card). Same for the other If _error_ if soc.

See what happen, will cost less than a few minutes.

When assumption is right you see obs>0 when still 0 there is something different.

As you say with txt-files it is working but with csv not. For me a csv-file is just a special text file where a special chosen character has the meaning of column delimiter.
You are possible confused with those suffixes of files, being believed having a special meaning. Do not be that easy in your believe.
Just at Windows this done with associations to find an associate program. It makes live easy that way. At Unix and Mainframe they are also existing but associations? no way.
You are liking probably zip-files. For fun docx xlsx egp pkg and much more are zip-files with a dedicated content for some programs

Re: Reading multiple files

Aman4SAS — Fri, 02 May 2014 06:23:18 GMT

Thanks for your input.

But your code will not work if u have space in your path. Thatsy i need to use create macro variable with nrbquote.

Rest is fine,

Thanks once again.

Re: Reading multiple files

jakarman — Fri, 02 May 2014 06:50:02 GMT

Your are right with the spaces being a challenge. The easiest is to avoid them. When you are not able to avoid....
Working wiht a CLI ("Command Line Interface" Dos-command ssh TSO) you will also have to know how to deal that. When a filename contains spaces it needed to get quoted " at Window ' at Unix. These quotes are also needed at your SAS environment. That is very difficult coding in SAS macro as quotes also have a meaning in SAS. Knowing that requirement and the things around it is able to get to work. Yes solving it with the required quotes in the right context spaces in path will work.

Re: Reading multiple files

Aman4SAS — Fri, 02 May 2014 06:57:31 GMT

No its not a challenge.

just use macro variable with %nrbquote as i did in my code.

Rest is your code is working fine. and i m getting result.

I am agree with TOM that problem was in subsetting or soccode macro.

I found problem was in soccode macro which u resolved very well. i am thankful to you.

Thanks Again.

Re: Reading multiple files

Aman4SAS — Fri, 02 May 2014 07:32:50 GMT

My Apology , I forgot to see whom i response. I thought its MikeZdeb and shared what i tried in my code.

I do not have 1% knowledge compare than you to let you know what is right or wrong.

Please accept my apology.

Re: Reading multiple files

jakarman — Fri, 02 May 2014 08:13:08 GMT

No problem. You solved your issue. And the macro quoting you are having what is behind there.

Re: Reading multiple files

Aman4SAS — Fri, 02 May 2014 09:09:03 GMT

I have used nrbquote to quote the path

%let dirname = %nrbquote("..\temp");

%let dirname2 = ..\temp;

%put &dirname &dirname2;

filename DIRLIST pipe "dir /B &dirname\*.csv";

data dirlist ;

length fname $256;

infile dirlist length=reclen ;

input fname $varying256. reclen ;

run;

proc print data = dirlist;

run;

Re: Reading multiple files

jakarman — Fri, 02 May 2014 09:39:48 GMT

Not quite what I meant. the string ..\temp is not having big issues but when your path/filename is needing quotes around for Window/Unix the name be like

'..\tmp space\your - dataset.csv' The those quotes are easily conflicting with SAS quoting as variable xcmd or what ever.

http://www2.sas.com/proceedings/sugi28/011-28.pdf is a nice document about macro quoting. The filename quoting is mentioned, see macro mkfn.

Re: Reading multiple files

Aman4SAS — Fri, 02 May 2014 10:15:33 GMT

my apology...

I think i havent write my ans very clear.

NRBQUOTE will require only when there is a space in ur path i.e "I:\temp folder\ sas tutorial"

i used ..\temp in my ans just like that.

there is no need to quote when no space in ur path i.e. I:\temp_folder\sas_tutorial\

I hope this time i m clear