How to split character and numeric data from a single column

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

How to split character and numeric data from a single column

Hello SAS Community!

 

I need to split the address column into two new columns, City and ZipCode. 

hw3.jpg

data AgentD;
infile '/home/Homework 3/AgentD.txt' firstobs=2;
length Customer 8 Date1 8 Date2 8 Address $13;
input Customer Date1 : YYMMDD10. Date2 : YYMMDD10. Address $ &;
run;
proc print data=agentd;
title 'AgentD';
run;

Any ideas on how to do this?


Accepted Solutions
Solution
2 weeks ago
PROC Star
Posts: 831

Re: How to split character and numeric data from a single column

[ Edited ]
Posted in reply to newbie_grad

use scan function

data AgentD;
infile '/home/Homework 3/AgentD.txt' firstobs=2;
length Customer 8 Date1 8 Date2 8 Address $13;
input Customer Date1 : YYMMDD10. Date2 : YYMMDD10. Address $ &;
City=scan(address,1,',');
zipcode=scan(address,-1,',');
run;
proc print data=agentd;
title 'AgentD';
run;

View solution in original post


All Replies
Solution
2 weeks ago
PROC Star
Posts: 831

Re: How to split character and numeric data from a single column

[ Edited ]
Posted in reply to newbie_grad

use scan function

data AgentD;
infile '/home/Homework 3/AgentD.txt' firstobs=2;
length Customer 8 Date1 8 Date2 8 Address $13;
input Customer Date1 : YYMMDD10. Date2 : YYMMDD10. Address $ &;
City=scan(address,1,',');
zipcode=scan(address,-1,',');
run;
proc print data=agentd;
title 'AgentD';
run;
Occasional Contributor
Posts: 9

Re: How to split character and numeric data from a single column

Posted in reply to novinosrin

That worked with some formatting. Now how can I make the ZipCode column a numerical value?

data AgentD;
infile '/home/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=scan(address,-1,',');
keep Customer Date1 Date2 City ZipCode;
run;
proc print data=agentd;
title 'AgentD';
run;
PROC Star
Posts: 831

Re: How to split character and numeric data from a single column

Posted in reply to newbie_grad

use input around the scan like:

ZipCode=input(strip(scan(address,-1,',')),5.);

 

 

☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 68 views
  • 2 likes
  • 2 in conversation