Combining data sets into one temporary data set.

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Combining data sets into one temporary data set.

I am having trouble combining four data sets that came from .txt files on my computer into one new temporary data set. I have attached the four .txt files. The SAS code I have used is below. The first 4 data steps are for reading the files into SAS; then I sorted the data sets by Customer, and then tried to merge. But when I merged them I only got the 25 observations from the AgentD data set. Any ideas on how to get all four of them into one set (like one on top of the next)?

 

data AgentA;
infile '/home/jadencarlson0/STAT-725/Homework 3/AgentA.txt' firstobs=2;
length Customer 8 Date1 8 Date2 8 City $24 ZipCode 8;
input Customer Date1 : YYMMDD10. Date2 : YYMMDD10. City $ & ZipCode;
format Date1 Date2 YYMMDD10.;
run;
proc print data=AgentA;
title 'AgentA';
run;

data AgentB;
infile '/home/jadencarlson0/STAT-725/Homework 3/AgentB.txt' firstobs=2;
length Customer 8 Date1 8 Date2 8 City $24 ZipCode 8;
input Customer Date1 : YYMMDD10. Date2 : YYMMDD10. City $ & Zipcode;
format Date1 Date2 YYMMDD10.;
run;
proc print data=AgentB;
title 'AgentB';
run;

data AgentC;
infile '/home/jadencarlson0/STAT-725/Homework 3/AgentC.txt' firstobs=2;
length Customer 8 Date1 8 Date2 8 City $24 ZipCode 8;
input Customer Date1 : YYMMDD10. Date2 : YYMMDD10. City $ & Zipcode;
format Date1 Date2 YYMMDD10.;
run;
proc print data=AgentC;
title 'AgentC';
run;

data AgentD;
infile '/home/jadencarlson0/STAT-725/Homework 3/AgentD.txt' firstobs=2;
length Customer 8 Date1 8 Date2 8 Address $24;
input Customer Date1 : YYMMDD10. Date2 : YYMMDD10. Address $ &;
format Date1 Date2 YYMMDD10.;
City=scan(address,1,',');
ZipCode=input(strip(scan(address,-1,',')),5.);
keep Customer Date1 Date2 City ZipCode;
run;
proc print data=agentd;
title 'AgentD';
run;

proc sort data=AgentA;
by Customer;
run;
proc sort data=AgentB;
by Customer;
run;
proc sort data=AgentC;
by Customer;
run;
proc sort data=AgentD;
by Customer;
run;
data Combined;
merge AgentA AgentB AgentC AgentD;
by Customer;
run; proc print data=Combined; run;

Accepted Solutions
Solution
2 weeks ago
Super User
Posts: 12,148

Re: Combining data sets into one temporary data set.

Set the 4 data sets together as @novinosrin suggests. The code set Agent: ; with the : says to use all data sets that start with Agent. If you have other sets that start with Agent then list the ones you want to use.

 

Then sort by customer and possibly your date variables After combining them.

View solution in original post


All Replies
PROC Star
Posts: 831

Re: Combining data sets into one temporary data set.

Posted in reply to newbie_grad

You shouldn't be merging. you should be appending. Try

 

data combined;

set agent:;

run;

Super User
Posts: 21,546

Re: Combining data sets into one temporary data set.

Posted in reply to newbie_grad

MERGE -> add columns to your data set

APPEND -> add rows to your data set

 

So you want rows. 

Solution
2 weeks ago
Super User
Posts: 12,148

Re: Combining data sets into one temporary data set.

Set the 4 data sets together as @novinosrin suggests. The code set Agent: ; with the : says to use all data sets that start with Agent. If you have other sets that start with Agent then list the ones you want to use.

 

Then sort by customer and possibly your date variables After combining them.

Occasional Contributor
Posts: 9

Re: Combining data sets into one temporary data set.

That all worked. Now I need to make a new column titled 'Agent' for which Agent .txt file the data came from in this new combined data set. So the first 25 records would say A, the next B, the next C, and the final 25 would be labeled D. 

data Combined;
set Agent:;
run;
proc print data=Combined;
run;
PROC Star
Posts: 831

Re: Combining data sets into one temporary data set.

Posted in reply to newbie_grad

look for indsname= in set statement option. im eatin my lunch now, cant type properlyto help

Super User
Posts: 21,546

Re: Combining data sets into one temporary data set.

Posted in reply to newbie_grad
data Combined;
set Agent: indsname=source;
dsn=source;
run;

You could also modify your initial read in program to read them all into the same file at once but I think this would solve your problem.

 

https://communities.sas.com/t5/SAS-Communities-Library/How-do-I-write-a-macro-to-import-multiple-tex...

Occasional Contributor
Posts: 9

Re: Combining data sets into one temporary data set.

What if I just want it to say A, B, C, or D instead of WORK.AGENTA?

kl.jpg

data Combined;
title 'Combined DataSet';
set Agent: indsname=source;
Agent=source;
run;
proc print data=Combined;
run;
PROC Star
Posts: 831

Re: Combining data sets into one temporary data set.

Posted in reply to newbie_grad

lol you are being cheeky, aren't you ? hahaha

why did you name 

data AgentA;
data AgentB; 

in the first place? 

Occasional Contributor
Posts: 9

Re: Combining data sets into one temporary data set.

Posted in reply to novinosrin

@novinosrinHaha! I'm just trying to make my professor happy! The current code makes sense to me, but I know I'm going to get points off. So I changed all my Data to 

Data A;

Data B;

etc. like you suggested and it still came up with the same WORK.AGENTA in the columns...At this point, I'll probably just leave it, but I greatly appreciate @novinosrin and @Reeza 's responses today. Literally couldn't have gotten through this without you both! Thanks!

 

PROC Star
Posts: 831

Re: Combining data sets into one temporary data set.

Posted in reply to newbie_grad

@newbie_grad All the best mate!. This forum is very addictive. Try to get on here more often. I am off for my midterm exam at my college in an hour. I was so stressed but your post let go of my stess. Smiley Happy Take care

Super User
Posts: 21,546

Re: Combining data sets into one temporary data set.

Posted in reply to newbie_grad

If you haven't used a naming convention you need to explicitly list all the data sets.  Try the following to import all at once:

 


data import_all;
 
*make sure variables to store file name are long enough;
length filename txt_file_name $256;
length Customer 8 Date1 8 Date2 8 Address $24;
 
*keep file name from record to record;
retain txt_file_name;
 
*Use wildcard in input;
infile '/home/jadencarlson0/STAT-725/Homework 3/*.txt' firstobs=2 eov=eov filename=filename truncover;
 
*Input first record and hold line;
input@;
 
*Check if this is the first record or the first record in a new file;
*If it is, replace the filename with the new file name and move to next line;
if _n_ eq 1 or eov then do;
txt_file_name = scan(filename, -1, "\");
eov=0;
end;
 
*Otherwise  go to the import step and read the files;
else input Customer Date1 : YYMMDD10. Date2 : YYMMDD10. Address $ &;
 
City=scan(address,1,',');
ZipCode=input(strip(scan(address,-1,',')),5.);
keep Customer Date1 Date2 City ZipCode;

;
run;
Occasional Contributor VDD
Occasional Contributor
Posts: 14

Re: Combining data sets into one temporary data set.

Merge also updates columns in dataset a with column values from dataset b.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 170 views
  • 6 likes
  • 5 in conversation