BookmarkSubscribeRSS Feed
venkatnaveen
Obsidian | Level 7

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

U

15 REPLIES 15
Tom
Super User Tom
Super User

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 ;

Ravikumarkummari
Quartz | Level 8

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

Tom
Super User Tom
Super User

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.

Ravikumarkummari
Quartz | Level 8

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;

%end;

%end;

%mend years;

%years;

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

Reeza
Super User

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

Ravikumarkummari
Quartz | Level 8

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.

Reeza
Super User

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

Reeza
Super User

Eerily similar to this question:

venkatnaveen
Obsidian | Level 7

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

Reeza
Super User

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

Peter_C
Rhodochrosite | Level 12

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

Peter_C
Rhodochrosite | Level 12

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

Ravikumarkummari
Quartz | Level 8

Hi

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

Reeza
Super User

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.

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
  • 15 replies
  • 1592 views
  • 0 likes
  • 5 in conversation