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

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;
1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

12 REPLIES 12
novinosrin
Tourmaline | Level 20

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

 

data combined;

set agent:;

run;

Reeza
Super User

MERGE -> add columns to your data set

APPEND -> add rows to your data set

 

So you want rows. 

ballardw
Super User

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.

newbie_grad
Fluorite | Level 6

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;
novinosrin
Tourmaline | Level 20

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

Reeza
Super User
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...

newbie_grad
Fluorite | Level 6

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;
novinosrin
Tourmaline | Level 20

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

why did you name 

data AgentA;
data AgentB; 

in the first place? 

newbie_grad
Fluorite | Level 6

@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!

 

novinosrin
Tourmaline | Level 20

@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. 🙂 Take care

Reeza
Super User

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;
VDD
Ammonite | Level 13 VDD
Ammonite | Level 13

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

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
  • 12 replies
  • 1726 views
  • 6 likes
  • 5 in conversation