DATA Step, Macro, Functions and more

SAS Ouput from reading 2 input file

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

SAS Ouput from reading 2 input file

Hello all - new to this community- 1st question ever

 

I have a fixed length data file of 18,000+ records (this will be known as File A)

i have a fixed length data file of 83 records (this will be known as File B)

My output file will need be a fixed length file of those same 18,000 records with a new variable added.

 

File B only contains one field, and it is a variable number (1st observation is "72", 2nd is "144" etc...) this will change monthly.

 

What I need is help with a program that will do this:

 

I need to read the 1st observation of File B and store it's value - in this case "72"

 

I need to then go back to file A and output the number of records stored from the 1st observation of File B (72) and add a new variable that contains sequential number...for example.1-0001 for File A Record # 1, 1-0002 for File A Record # 2 ...etc .. etc until I reach the last number that I got from File B (72) ... once I am there, I need to then read the 2nd Observation from File B (144), then repeat the above process of going back to File A and output that number of records with a variable that contains a 2nd number of "2-0001", 2-0002" etc etc.

 

I have tried a few things with data sets, but I am not getting the 18,000 records to output. This is beyond what I've ever used SAS for, and I'm far from an expert in this language.

 

Any help you could give would be appreciated.

 

Tthanks in advance!


Accepted Solutions
Solution
‎04-26-2018 03:52 PM
Super User
Posts: 6,935

Re: SAS Ouput from reading 2 input file

Posted in reply to brianberan

Based on your subsequent description, here are a few changes to make.

 

There's a missing dot at the end here:

 

@014 fillb $char138

 

The FILE statement has to be moved.  It becomes part of the final DATA step (after the DATA statement, but before any PUT statements).

 

The LRECL on the FILE statement might need to be lengthened.  You're using the same number of characters that you started with, before allowing for the key.

 

When writing the records back out ...

 

There is no variable named dash:

 

@006 dash $char001.

 

SImply use:

 

@006 '-'

 

To get leading zeros, use formats for writing _N_ and k:

 

@004 _N_ Z2.

 

@007 k Z4.

 

I think that does it ... give it a shot and see what you get.

View solution in original post


All Replies
Super User
Posts: 6,935

Re: SAS Ouput from reading 2 input file

Posted in reply to brianberan

We have to begin with the assumption that you know how to read your fixed-column files into SAS data sets.  Once you have done that:

 

data want;

set B;

do k=1 to b_field;

   set A;

   key = put(_n_, z2.) || '-' || put(k, z4.);

   output;

end;

drop k;

run;

 

I actually created your new variable as 01-0001 instead of 1-0001, since its known that you will need to go up to 83.

 

The number of observations you get from this could be considerably less than 18,000 since it is limited by the sum of the values coming in from file B.

 

If you actually want text files, and not SAS data sets, the same approach can be used.  But the programming is a little more complex and requires that you specify more exactly what the output should look like.

Occasional Contributor
Posts: 5

Re: SAS Ouput from reading 2 input file

Posted in reply to Astounding

thanks for the Reply, I understand this and will give it a shot.  Your assumption is correct, the Total of File B will always match the Quantity of Records in File A.  

 

File B is basically a "shipping file" and contains names and addresses of locations.  

 

The project is basically ... take the first 72 records and ship them to destination # 1 ... take the next 144 records and ship them to destination 2. I need to print this number on my mailing piece for each record so that the production folks will know when to "break" a destination.

 

I certainly need to output a new fixed length file ... it will be all of the records in the File A, just with a new variable added.

Super User
Posts: 13,941

Re: SAS Ouput from reading 2 input file

Posted in reply to Astounding

And if you want that key field as the first column:

data want;
set B;
do k=1 to b_field;
   key = put(_n_, z2.) || '-' || put(k, z4.);
   set A;
   output;
end;
drop k b_field;
run;

You likely want to drop the value from B in the final result as well.

 

Occasional Contributor
Posts: 5

Re: SAS Ouput from reading 2 input file

Posted in reply to Astounding

ok ... this is kind of the work in progress ... I am not getting any errors, but I'm also not getting any records to output:

 

data paneldat;
infile 'C:\Data\xxxx\Corporate\100410\Final-DBFs\use4sas-C.dat' lrecl=153 recfm=f;
input @001 filla $char003.
@014 fillb $char138
@152 crlf $char002.;

 

data varqty;
infile 'c:\data\xxxx\corporate\100410\final-DBFs\C-Qty.dat' lrecl=12 recfm=f;
input @001 outqty 10.;


file 'c:\data\xxxx\corporate\100410\final-DBFs\outsas-Test.dat' lrecl=153 recfm=f;

 

 

data want;
set work.varqty;
do k=1 to outqty;
set work.paneldat;

put @001 fillA $char003.
@004 _N_ 2.
@006 dash $char001.
@007 k 4.
@014 fillB $char138.
@152 crlf $char002.;

 

end;
drop k;
run;

 

Any thoughts?

Super User
Posts: 6,935

Re: SAS Ouput from reading 2 input file

Posted in reply to brianberan

We'll find a way to shorten it, but it definitely has a lot of the right pieces.  From what you've written, let me confirm the objective here.

 

In the new text file, you want the key as the first part of every line (such as 01-0001 then the text from file A on the same line).  You don't want the key on a separate line by itself, followed by separate lines of text that don't contain the key.

Occasional Contributor
Posts: 5

Re: SAS Ouput from reading 2 input file

Posted in reply to Astounding

thanks a lot for all your help, I truly appreciate this.

 

If you look at my first Infile for what I am calling "Paneldat", you will see there is a gap of space between Position # 4 and Position 13 ... this is where I want my Key to be in the output

 

my output will be fixed length:

position # 1 will be the FillA data from the PanelDat file for a length of 3.

position # 4 will be the Key (it can take up as many as 10 bytes, doesn't matter to much)

position #14 will be the FillB data from the PanelDat.

 

Solution
‎04-26-2018 03:52 PM
Super User
Posts: 6,935

Re: SAS Ouput from reading 2 input file

Posted in reply to brianberan

Based on your subsequent description, here are a few changes to make.

 

There's a missing dot at the end here:

 

@014 fillb $char138

 

The FILE statement has to be moved.  It becomes part of the final DATA step (after the DATA statement, but before any PUT statements).

 

The LRECL on the FILE statement might need to be lengthened.  You're using the same number of characters that you started with, before allowing for the key.

 

When writing the records back out ...

 

There is no variable named dash:

 

@006 dash $char001.

 

SImply use:

 

@006 '-'

 

To get leading zeros, use formats for writing _N_ and k:

 

@004 _N_ Z2.

 

@007 k Z4.

 

I think that does it ... give it a shot and see what you get.

Occasional Contributor
Posts: 5

Re: SAS Ouput from reading 2 input file

Posted in reply to Astounding

Thanks again!  All good now ... the key was certainly to move that output file line ... I accidentally deleted a few of the lines on my original post (like the period at the end of 138) and the assignment of my "dash" internal variable to '-';, but that's because I was trying to edit/clean things up to make it easier for you guys to read, and then I realized it wasn't going to format well. :-)

 

Anyhow, I forgot that I needed the "group" counter or variable "k" to be first, so I switched it around on the output and converted the _N_ to an alpha field so I could trim the space off of it ... so the Key output looks like 0001-1 instead of 1-0001.

 

You're the best ... here is the final code — works like a champ — I'm always happy to learn something new.

 

data paneldat;
infile 'C:\Data\LOEB\Corporate\100410\Final-DBFs\use4sas-C.dat' lrecl=153 recfm=f;
input @001 filla $char003.
@014 fillb $char138.
@152 crlf $char002.;

 

data varqty;
infile 'c:\data\LOEB\corporate\100410\final-DBFs\C-Qty.dat' lrecl=12 recfm=f;
input @001 outqty 10.;

 

data want;
set work.varqty;
do k=1 to outqty;
set work.paneldat;
a_N_ = trim(left(_N_));

 

file 'c:\data\LOEB\corporate\100410\final-DBFs\outsas-Test.dat' lrecl=153 recfm=f;
put @001 fillA $char003.
@004 k z4.
@008 '-'
@009 a_N_ $char002.
@014 fillB $char138.
@152 crlf $char002.;
end;
drop k;
run;

 

PROC Star
Posts: 631

Re: SAS Ouput from reading 2 input file

Posted in reply to brianberan

It is not clear on what your trying to do. The best way to make other to understand your problem is present some sample data like you have and the output your expecting. 

 

Please check this on how to create a sample data. https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

 

Thanks,
Suryakiran
☑ This topic is solved.

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

Discussion stats
  • 9 replies
  • 168 views
  • 1 like
  • 4 in conversation