DATA Step, Macro, Functions and more

Need help with importing data

Accepted Solution Solved
Reply
Contributor
Posts: 41
Accepted Solution

Need help with importing data

[ Edited ]

I'm trying to read in this set of data and I need some help:

 

A123456789,A,3
1,Y,15Feb1960,M,M,3,FT,55000
2,N,3Jun1965,F,M,3,UE,0
A135790234,B,1
1,Y,19Oct1944,F,D,0,NA,3000
B234523456,A,2
1,Y,30Jun1978,F,M,1,PT,4000
2,N,21May1975,M,M,2,FT,30000

 

I need the output to look like this:

 

image 12.png

I have come up with this solution which works fine:

 

Data Yourdata (drop=temp);
Infile datalines dsd;
retain var1-var3;
input temp : $10. @;
if length(temp) ^= 1 then do;
Input @1 var1 $10.
      @12 var2 $
      var3 $
      var4
      var5 $
      var6 : date9.
      var7 $
      var8 $
      var9
      var10 $
      var11 ;
end; else do;
Input @1 var4
      var5 $
      var6 : date9.
      var7 $
      var8 $
      var9
      var10 $
      var11 ;
end;
format var6 date9.;
datalines;
A123456789,A,3
1,Y,15Feb1960,M,M,3,FT,55000
2,N,3Jun1965,F,M,3,UE,0
A135790234,B,1
1,Y,19Oct1944,F,D,0,NA,3000
B234523456,A,2
1,Y,30Jun1978,F,M,1,PT,4000
2,N,21May1975,M,M,2,FT,30000
;
run;

However, I'm partially using formatted input instead of list input and I'm ignoring the delimiter for some of the columns. I am wondering if there is a better solution for this.

 

Can anyone help? Thanks Smiley Happy

 


Accepted Solutions
Solution
‎10-26-2017 02:13 PM
PROC Star
Posts: 8,115

Re: Need help with importing data

Here is one possible alternative:

Data Yourdata;
  infile datalines dsd delimiter=',';
  informat var1 $10.;
  informat var2 var3 $1.;
  informat var4 8.;
  informat var5 $1.;
  informat var6 date9.;
  informat var7 var8 $1.;
  informat var9 8.;
  informat var10 $2.;
  informat var11 8.;
  retain var1-var3;
  input @;
  if length(scan(_infile_,1,',')) ^= 1 then do;
    Input var1
          var2
          var3;
  end;
  else do;
    input var4
      var5
      var6
      var7
      var8
      var9
      var10
      var11 ;
    output;
  end;
  format var6 date9.;
  datalines;
A123456789,A,3
1,Y,15Feb1960,M,M,3,FT,55000
2,N,3Jun1965,F,M,3,UE,0
A135790234,B,1
1,Y,19Oct1944,F,D,0,NA,3000
B234523456,A,2
1,Y,30Jun1978,F,M,1,PT,4000
2,N,21May1975,M,M,2,FT,30000
;
run;

Art, CEO, AnalystFinder.com

View solution in original post


All Replies
Trusted Advisor
Posts: 1,826

Re: Need help with importing data

[ Edited ]

Assuming your input data is in a consistent format, that is - always 3 lines and all variables exist (even if some of them have missing value) then you can read the 3 lines with one INPUT staement, in a format of:

  input
    <first line variables> /
    <second line variables> /
    <third line variables>
 ;

The slash (/) tells sas to continue reading from next line.

Contributor
Posts: 41

Re: Need help with importing data

No unfortunately I need the first three columns to "retain" to the next line for some of the rows:

 

image 13.png

 

I don't think the slashes would work. Also, the DSD option already changes the delimiter to a comma. I don't think the DLM="," option is needed...

Trusted Advisor
Posts: 1,826

Re: Need help with importing data

[ Edited ]

OK, I see - you have a prefix line to concatenate with next one or more lines,

 where each of them - except the prefix line - creates an output observation.

 

Then your code is perfct. I wouldn't change it.

Contributor
Posts: 41

Re: Need help with importing data

No it goes like this:

 

Raw data:

A123456789,A,3
1,Y,15Feb1960,M,M,3,FT,55000
2,N,3Jun1965,F,M,3,UE,0

 

Desired output:
A123456789 A 3 1 Y 15Feb1960 M M 3 FT 55000
A123456789 A 3 2 N 3Jun1965 F M 3 UE 0

 

The first three columns are "header" columns and they should retain to the next line for some of the rows.

Contributor
Posts: 41

Re: Need help with importing data

That's correct Smiley Happy

 

I don't like the fact that some columns are read in using formatted input when the data are separated by a comma:

 

Input @1 var1 $10.
      @12 var2 $
      var3 $
      var4

 

The VAR1 column had to be re-read" in since I had the TEMP column there. Re-reading data cannot be done using list input and the only solution I can think of is to use the formatted input.

 

Just curious if there is a better way around...

Trusted Advisor
Posts: 1,826

Re: Need help with importing data

Allso add option dlm=',' to the infile staement:
Infile datalines dsd dlm=',';
Solution
‎10-26-2017 02:13 PM
PROC Star
Posts: 8,115

Re: Need help with importing data

Here is one possible alternative:

Data Yourdata;
  infile datalines dsd delimiter=',';
  informat var1 $10.;
  informat var2 var3 $1.;
  informat var4 8.;
  informat var5 $1.;
  informat var6 date9.;
  informat var7 var8 $1.;
  informat var9 8.;
  informat var10 $2.;
  informat var11 8.;
  retain var1-var3;
  input @;
  if length(scan(_infile_,1,',')) ^= 1 then do;
    Input var1
          var2
          var3;
  end;
  else do;
    input var4
      var5
      var6
      var7
      var8
      var9
      var10
      var11 ;
    output;
  end;
  format var6 date9.;
  datalines;
A123456789,A,3
1,Y,15Feb1960,M,M,3,FT,55000
2,N,3Jun1965,F,M,3,UE,0
A135790234,B,1
1,Y,19Oct1944,F,D,0,NA,3000
B234523456,A,2
1,Y,30Jun1978,F,M,1,PT,4000
2,N,21May1975,M,M,2,FT,30000
;
run;

Art, CEO, AnalystFinder.com

Contributor
Posts: 41

Re: Need help with importing data

Awesome! That's exactly what I was looking for. Thanks!
☑ This topic is solved.

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

Discussion stats
  • 8 replies
  • 154 views
  • 1 like
  • 3 in conversation