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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

9 REPLIES 9
Astounding
PROC Star

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.

brianberan
Calcite | Level 5

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.

ballardw
Super User

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.

 

brianberan
Calcite | Level 5

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?

Astounding
PROC Star

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.

brianberan
Calcite | Level 5

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.

 

Astounding
PROC Star

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.

brianberan
Calcite | Level 5

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;

 

SuryaKiran
Meteorite | Level 14

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

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
  • 9 replies
  • 1098 views
  • 1 like
  • 4 in conversation