topic Re: How to automate? in SAS Programming

How to automate?

venkatnaveen — Sun, 25 Jan 2015 04:40:33 GMT

Hiiiii all,
solve this query
suppose some insurance data is availabe in D:\kumar folder.
in that year wise information is there. in folders ex:2003-2013(these are 10 folders)
again in each folder sub folders are there for month wise january-june.
and in each month some datasets are there respective to that month.
example in january information is in the form of datasets like(data_a, data_b, data_c) same type of data sets are availabe in all months.
now you should got my point.
(2003)-(january-june), (data_a,data_b,data_c)
-
-
-
-
-
2013-,(january-june) , (data_a,data_b,data_c)
now i have question that i need final output is "final_data_a" , "final_data_b", "final_data_c"
the final_data_a is contain all the ("data_a") datasets in all over 10years years. i.e. merging of all 2003-2013 (january-june) data_a(dataset)
how to automate this code can any one explain by using sas-macros

Re: How to automate?

Tom — Sun, 25 Jan 2015 05:12:02 GMT

Are these SAS datasets or raw data files that need to be read in?

Let's assume that they are data sets. One easy way is to get the names into a dataset. If you can use OS commands then this is easier.

So here is a program to look for all of the SAS datasets under the kumar folder and concatenate those with the same member name.

data files ;

infile 'dir D:\kumar\*.sas7bdat /s/b' pipe truncover ;

input fname $256. ;

length ftype $32 ;

ftype = scan(fname,-2,'.\');

run;

proc sort ;

by ftype fname ;

run;

filename code temp;

data _null_;

file code ;

set files;

by ftype ;

if first.ftype then put

'data final_' ftype ';'

/ ' set '

;

put fname :$quote. ;

if last.ftype then put

';'

/ 'run;'

;

run;

%inc code / source2 ;

Re: How to automate?

Reeza — Sun, 25 Jan 2015 06:04:34 GMT

Eerily similar to this question:

Re: How to automate?

venkatnaveen — Sun, 25 Jan 2015 06:08:23 GMT

Yes thats the question asked by friend.I have posted on his behalf.

Re: How to automate?

Reeza — Sun, 25 Jan 2015 06:31:22 GMT

Well he hasn't provided enough information by far to get any useful answers. We can make giant guesses and they'll probably be wrong.

For starters:

Are the files text files, excel files, sas datasets?

Do all the files have the same structure?

What version of SAS, can naming shortcuts be used which are in SAS 9.3+?

Does he mean merge i.e. add variables side by side or actually append, stack the data on top of each other?

What OS and does he have access to pipe commands to create a directory listing from SAS?

Also, he should try something as well, this is a FAQ and there are many solutions on here.

How do I ask a good question? - Help Center - Stack Overflow

Re: How to automate?

Peter_C — Sun, 25 Jan 2015 10:52:54 GMT

if this question is academic, I would suggest asking the tutor.

If it is professional/commercial, the client/employer needs better advice than expecting an employee who does not have the skills, to provide the answer

Re: How to automate?

Peter_C — Sun, 25 Jan 2015 10:58:53 GMT

since the path in the question contains a drive reference like D:\kumar folder.

use dos box to create a file list with dos command like:

dir /b/s D:\kumar_folder > c:\temp\kumar_filelist.txt

Then SAS can use that list to read all those files, or just to read the filelist

If the former, have a look at the FILEVAR option of the INFILE statement in the online doc and examples.

Message was edited by: Peter Crawford : needed to fix typo and the sense......

Re: How to automate?

Ravikumarkummari — Sun, 25 Jan 2015 16:50:03 GMT

Acutally i was posted this question.

I am giving some more additional information

The files all are having datasets.

the datasets having the same structure i.e. same variables ex p_id, p_name, p_amt(policy id, policy holder name, policy amount)

i am using sas 9.2 version. windows 7 operating system

we have to merge all this datasets by using datastep merge but the final output only 3 three datasets that are having all years information

and last the final dataset is only one i.e. final_data it contains all 10years of information

Message was edited by: ravikumar kummari

Re: How to automate?

Reeza — Sun, 25 Jan 2015 17:00:07 GMT

Can you provide the code for how you would do this 'manually' i.e. without macros?

Then perhaps someone can suggest more efficient or a macro solution.

Re: How to automate?

Ravikumarkummari — Tue, 27 Jan 2015 06:32:38 GMT

can some one help me through this code plsss

Re: How to automate?

Ravikumarkummari — Tue, 27 Jan 2015 10:13:10 GMT

Thanks sir for such a great code its working i am thankful to you

But i am not able to understand some part of it. If you don't mind can you explain this step

data _null_;

file code ;

set files;

by ftype ;

if first.ftype then put

'data final_' ftype ';'

/ ' set '

;

put fname :$quote. ;

if last.ftype then put

';'

/ 'run;'

;

run;

I think there is a depth logic in this. Now i am a learner it is difficult for me to understand. please sir explain

Re: How to automate?

Tom — Tue, 27 Jan 2015 13:06:43 GMT

1) Start a data step that will not create any output datasets .

2) Direct PUT statement output to a file.

3 and 4) Read in the list of files in the order of FTYPE variable ;

5) When you read the first observation for a group of records with the same FTYPE value then write out a DATA statement and the beginning of a SET statement. Use the value of FTYPE as part of the data set name in the DATA statement.

6) Write the name of the SAS dataset in quotes. The : modifier will make the quotes not include the trailing blanks in the vlaue fo FNAME. You can use this method to reference a SAS dataset directly instead of the traditional LIBNAME.MEMNAME format.;

7) When you get to the last observation in a group then write the semicolon to end the SET statement and a RUN statement to end the data step.

😎 End the DATA _NULL_ step.

Re: How to automate?

Ravikumarkummari — Wed, 28 Jan 2015 12:52:02 GMT

Hi Reeza sir i tried this task and below the code

In D drive policy_folder the data is available. There will be 5 years in that. 2003 to 2007. In that subfolders are there month wise

I taken months names from 1 to 6. and the datasets are in the form of ds_1 to ds_4. and resultant dataset will be only one i.e. the merging all the datasets in all the years,

%macro years;

%do i=2003 %to 2007 ;

%do k=1 %to 6 %by 1;

libname W_&k.&i. "E:\policy_data\&i.\&k.";

%do j=1 %to 4 %by 1;

Proc append base=final data=W_&k.&i..ds_&j. force;

run;

%end;

%mend years;

%years;

The final dataset will be stored in work library with all merging records.

Re: How to automate?

Reeza — Wed, 28 Jan 2015 16:28:50 GMT

So what's your question now? It looks like the solution to your problem.

Re: How to automate?

Ravikumarkummari — Wed, 28 Jan 2015 16:37:05 GMT

No question initially i tried this task but i didn't get proper output. So that i posted in community.

Later again i worked on it. Then i got output by using this code. You said you are not trying and doing your work with others. So i am posted what i have done to get the final result. That's all.

Re: How to automate?

Reeza — Wed, 28 Jan 2015 16:40:13 GMT

You can mark you question as answered then, with your answer as the correct answer.