SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

separate dataset with 1 column into different columns

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

separate dataset with 1 column into different columns

Hi,

 I have ad a data with observations as:

2160048011442.293800000 -87.951300000

 

i want to separate each observation into different variable colums

2#1#60048#0#11#4#42.293800000 #-87.951300000# (DATA do NOT have this "#"). I cannot put this # for each observation as there are millions of observations.

 

var 1
1-1 (position)
1 (length)
var2
2-2
1
var3
3-7
5
var4
8-8
1
var5
9-10
2
var6
11-11
1
var7
12-24
13.7
var8
25-37
13.7

 

how should I code?

I appreciate your input.

Thanks

 

run;


Accepted Solutions
Solution
‎04-14-2018 01:45 PM
Super User
Posts: 23,320

Re: separate dataset with 1 column into different columns

[ Edited ]

You likely have what's known as a fixed width file. 


Assuming your record layout as posted, try the following, tested code:

 

data have;
infile cards;
input  var1  1-1
       var2  2-2  
       var3  3-7
       var4  8-8
       var5  9-10
       var6  11-11
       var7  12-24
       var8  25-37;
cards;
2160048011442.293800000 -87.951300000
;;;;
run;

 


runn wrote:

Hi,

 I have ad a data with observations as:

2160048011442.293800000 -87.951300000

 

i want to separate each observation into different variable colums

2#1#60048#0#11#4#42.293800000 #-87.951300000# (DATA do NOT have this "#"). I cannot put this # for each observation as there are millions of observations.

 

var 1
1-1 (position)
1 (length)
var2
2-2
1
var3
3-7
5
var4
8-8
1
var5
9-10
2
var6
11-11
1
var7
12-24
13.7
var8
25-37
13.7

 

how should I code?

I appreciate your input.

Thanks

 

run;



 

View solution in original post


All Replies
Solution
‎04-14-2018 01:45 PM
Super User
Posts: 23,320

Re: separate dataset with 1 column into different columns

[ Edited ]

You likely have what's known as a fixed width file. 


Assuming your record layout as posted, try the following, tested code:

 

data have;
infile cards;
input  var1  1-1
       var2  2-2  
       var3  3-7
       var4  8-8
       var5  9-10
       var6  11-11
       var7  12-24
       var8  25-37;
cards;
2160048011442.293800000 -87.951300000
;;;;
run;

 


runn wrote:

Hi,

 I have ad a data with observations as:

2160048011442.293800000 -87.951300000

 

i want to separate each observation into different variable colums

2#1#60048#0#11#4#42.293800000 #-87.951300000# (DATA do NOT have this "#"). I cannot put this # for each observation as there are millions of observations.

 

var 1
1-1 (position)
1 (length)
var2
2-2
1
var3
3-7
5
var4
8-8
1
var5
9-10
2
var6
11-11
1
var7
12-24
13.7
var8
25-37
13.7

 

how should I code?

I appreciate your input.

Thanks

 

run;



 

Occasional Contributor
Posts: 5

Re: separate dataset with 1 column into different columns

I appreciate Reeza but I have 1.5 million observation. It makes SAS slower if I past all the observations as a dateline in the code.

 

There should be some other way as well.

 

Thanks.

Super User
Super User
Posts: 7,936

Re: separate dataset with 1 column into different columns

[ Edited ]

runn wrote:

I appreciate Reeza but I have 1.5 million observation. It makes SAS slower if I past all the observations as a dateline in the code.

 

There should be some other way as well.

 

Thanks.


Why would you post the data lines in the code????

If you have the data in a text file then use a INFILE statement to tell SAS where to read the data.

Reeza's example is just to show you how your INPUT statement should look based on your description of your record layout.

☑ This topic is solved.

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

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