Multiple Output Records from a single SAS observation

Reply
Occasional Contributor
Posts: 7

Multiple Output Records from a single SAS observation

[ Edited ]

Hi, am Trying to determine a programmatic way using iterative loop logic(but open to any ideas that work) to create multiple(45) output records with specific variables, including 3 newly created ones with hard coded values after reading in Each observation in a SAS file.   Have researched for Days using Every combination of keywords I can think of but no luck.   Have found several articles/webpages re: just the opposite- reading multiple records & creating 1 sas observation but nothing to help solve the problem.   The entire task is somewhat deeper & have had luck w/the 1st 2 parts, but this 3rd part is proving to be a real headache....   Especially when it Seems some simple looping logic Should solve the problem.   The real kink in this process is outputting 6 var's #'d 1-45 on Each output record using the Same #- e.g.- var1 varr1 varrr1 varrrr1 varrrrr1 on the same record.   Please see attached do loop code that only writes out 2300 obs using a 100 obs input file- should be 4500(45 (occurr's) X 100 (obs).   2nd rec should be var2 varr2 varrr2 varrrr2, etc., all the way to 45.   Each var1-45, of which there are 6 #'d like this are on the SAS input file.

 

Have even reached out to prior coworkers who've suggested some Macro Do Loop logic but still no luck, even after numerous attempts.   Fast approaching deadline to get this done & do Not want to hardcode a data step 45 times to accomplish this- Ridiculous, esp. for an applications programmer!

 

Any thoughts/suggestions are More than welcome- no idea too crazy.

 

Thank you!

MDSASCoder

Grand Advisor
Posts: 10,211

Re: Multiple Output Records from a single SAS observation

It would help to provide a small example input file and what the desired output looks like for that input.

 

Your example code mixes up macro and datastep logic. I suspect that there is NO reason to use any macor variables and that arrays are the key to problem. But I can't tell what you really are attempting.

 

BTW an interated do loop like:

 

do i = 1 to 45;

  <repeated statements>

end;

will do the same operation 45 times.

When you add in the "i + 1;" in that loop you will get only get odd values of i and one more than half. The End of the iterated Do loop automatically increments the loop variable by one when encountered. So when you put the i+1 it increments once for your code and then a second time when hitting the End statement.

Occasional Contributor
Posts: 7

Re: Multiple Output Records from a single SAS observation

[ Edited ]

Thanks BallardW for your quick response.   The input file has approx 1500 var's on it & only need the vars1-45 used in the keep statement.   Understand your thoughts about the index functionality and only added that as a suggestion from a curr coworker & forgot to remove it.   Did'nt think that would solve the problem, but he thought the counter was not incrementing properly as initial tests only output 100 obs to the temp file.  

 

After commenting out the i + 1 ;  am getting strange Warnings in attached file that were being rec'd earlier that Appear to have nothing to do w/the executed code.

 

The basic premise of the project is to take each Observation in a SAS file & write out 3 diff rec types(C/D/L) to a Flat file for Each observation.  The L record is the only 1 of the 3 that Requires 45 recs Per SAS Observation be written based on the 6 SAS var's w/#'s 1-45 on the ends of them written correspondingly to each record- i.e.- var1 varr1 varrr1 varrrr1 to the 1st Rec Type L output & so on unitl the 45th Record is written to a Temp SAS file.   Each record also needs the 3 hardcoded var's written to Each of the 45 records also, which throws a bit of a kink into the process.  

 

Hope that's a little better clarity.

 

What I'm attempting to do given the time constraints(in lieu of better programming looping code to accomplish this) is writing out 3 diff SAS Temp files & Concatenating them back in by Rec_Type & claim_id(unique #- using _N_  or observation #) & then writing them out to the flat file after sorting them by Rec_type & Unique_id( _N_ ).   This process currrently works as the C/D records are correctly being written to the flat file.   The problem I'm having is getting the proper variables written to each Corresponding L record (45 for Each SAS observation read in) along with the 3 hardcoded var's prior to the Do Loop.

 

No macro var's are needed, but figured Do Looping logic Should solve this L record Type problem.  

 

Thanks! 

Grand Advisor
Posts: 17,356

Re: Multiple Output Records from a single SAS observation

I don't understand your problem.

It's too wordy. Can you break it into a smaller example?

 

Based on what you've currently stated something as simple as this may be what you need. At worst you're going to need some nested do loops.

 

data want;

set sashelp.class; *data set to be exported;

file 'C:\_localdata\temp.txt'; *text file to be created;

*Output header record;
if _n_ =1 then do;
put "Name, Sex, Age, Height, Weight, Record_Type";
end;

do record_type= "A", "B", "C";
*conditionally put records to file;
if record_type="A" then put Name Age Height Record_type;
*put all records out;
else
    put (_all_) (~);
end;

run;
Occasional Contributor
Posts: 7

Re: Multiple Output Records from a single SAS observation

Hi Reeza, understand but if I did'nt put the details in thought I'd be wasting others' time w/responses that would'nt work.

 

1 SAS obs in = Up to 47 records to a flat file out- each record type (C/D/L) with a different record format.

 

C/D are curr working.  L is a problem.   6 diff SAS vars on input file have var's that are #'d 1-45 on the end of each, hence 270(6 x 45) diff var's need to be read in for rec type L.   Each of the 45 L records Needs to have the 6 SAS var's & corresponding #'s on the Same record.  vara1 Varb1 VARC1 varD1- 6 SAS vars.   SAS vara1 is named the same, up to vara45.   Same for the other 5 SAS var's.

 

Hope that explains better.

Grand Advisor
Posts: 17,356

Re: Multiple Output Records from a single SAS observation

Can you post sample input data and what you expect for output? A picture is worht a thousand words.

You can reduce the vars and problem - basically create a MCVE

http://stackoverflow.com/help/mcve

Occasional Contributor
Posts: 7

Re: Multiple Output Records from a single SAS observation

Reeza/BallardW, would like to put some sample data out there to help better explain the problem but being confidential hospital data cannot.   Have re-attached some code w/dummy var's as well for related reasons.

 

Thanks for the MCVE link Reeza- good points- simple & succinct.

 

Will try & simplify my questions here, and have attached some sample code & log file using a Do Loop which currently is running but producing no output, errors, or warnings/notes.   SAS is being executed on a Windows Server in this environment.

 

As you see in the KEEP statement, only VarA1-45, VarB1-45 & so on through VarF1-45 are being kept.   These are varying char/num vars but their data types are not causing any problems here.

 

The end goal is to read 1 SAS obs & produce a Flat file w/3 Diff rec types(C/D/L), w/the L record having 45 occurrences.   Each rec type uses different vars & has different formats/layouts.   Rec Types C/D are currently coded & working as Temp SAS files & then Sorted & Concatenated & written to the flat file.   My goal is to use a Data step to process the L Rec Type w/45 diff records PER SAS observation read in.

 

Hope this helps along w/the attachments.  Sorry about the data input.

 

 

 

 

 

 

Grand Advisor
Posts: 10,211

Re: Multiple Output Records from a single SAS observation

I understand actual data may be confidential. If you can provide a dummy data set with the key features, you don't need to have 45 varaibles in the group, 3 would do. Also likely only 2 or 3 of the variable groups would suffice. Provide enough records, and please use multiple values for the variables as when they are all 1 it is very hard to see what transform you may have applied, to exercise the cycle a couple of times.

And then provide what the expected outcome matching that example input would be. Yes that will require you to do hand calculations but then we can see what is going on.

 

I had a math professor that used to say "trying to do math over the phone is useless". Coding is much the same. After you have working code you can document and explain the steps but without a clearcut example of the start and end of the process it is real hard to understand what you may mean by phrases such as  "using the Same #- e.g.- var1 varr1 varrr1 varrrr1 varrrrr1 on the same record".

Without much work I can come up with 2 meanings for that phrase and suspect that if I spent some time I could likely develop 4 or 5 more interpretations.

 

It also doesn't help when you have multiple code like:

VarA&i.= VarA&i.  ;

Which will never actually add any value to any data set. That is exactly the same as saying:

A=A;

with the added bit of likely misuse of macro variables.

We cannot even tell which of your variables on your keep statement:

 keep VarA1-VarA45 VarB1-VarB45 VarC1-VarC45 VarD1-VarD45 VarE1-VarE45 VarF1-VarF45 ;

may already exist in your data set (Input.SASFile).

New Contributor M2
New Contributor
Posts: 2

Re: Multiple Output Records from a single SAS observation

/* try this it illustrates a possible approach */


data test;
v1=1;
v2=2;
v3=3;
v4=4;
v5=5;
run;


filename f_out "C:\aa_new_racing\TEST\Txtfiles\test.txt";
data _null_;
file f_out;
set test;
array l_vars(3) v1-v3;
if v4 = 4
   then do;
          put @1 "C" @ 5 "V4 = " @10 v4 2.;
          end;
if v5 = 5
   then do;
          put @1 "D" @ 5 "V5 = " @10 v5 2.;
          end;
do i = 1 to 3;
             txt="V" || left(trim(put(i,2.))) || "=";
             x=l_vars(i);
             put @1 "L" @ 5 txt $5.  @10 x 2.;
             end;
run;

Occasional Contributor
Posts: 7

Re: Multiple Output Records from a single SAS observation

Thanks M2 & Everyone else for your insightful thoughts & valuable time.   An array MIGHT have been the solution, but Unfortunately will Never know now as my contract ended Friday just for Doing My Job...   The FTE I WAS training will now have to figure it out...

 

The REAL problem AND Key to resolving this issue WAS the fact there are 270 variables IN the SAS Input file #'d 1-45 on the END of Each variable combined with 3 new self-generated vars w/hardcoded values In that step for the L record that Need to be written out, 45 times....   Was thinking some combination of a Nested Do Loop might be required, but an array might Also have solved the problem, but will never know now...

 

Thanks Everyone for your valuable time and considerate offers to help....

Grand Advisor
Posts: 17,356

Re: Multiple Output Records from a single SAS observation

You may have needed a macro to generate the list of variables but the problem was never clear. 

 

I have some guesses as to what the issues may have been, you probably needed to generate lines of the form:

 

put varA1 - VarA45;
put varB1- VarB45;
put varC1-VarC45;

...


for all 45 different variations

Personally, I'd still want to know the solution, even if my contract was finished Smiley Happy

Occasional Contributor
Posts: 7

Re: Multiple Output Records from a single SAS observation

Reeza, I tried numerous times to clarify exactly what I Was trying to code for:

 

VarA1-VarA45

VarB1-VarB45

VarC1-VarC45

VarD1-VarD45

VarE1-VarE45

VarF1-VarF45

 

Where VarA1-VarF1 needs to be on the 1st record & VarA2-VarF2 needs to be on the 2nd record, and so on, up through record 45.   Don't know how I can be more clear.   ALL var's as stated previously Are on the Input SAS file, as I stated in my 1st post.

 

And, I'd Love Nothing More than getting the answer, but how can I possibly test Any suggestion w/out access to SAS?

Grand Advisor
Posts: 17,356

Re: Multiple Output Records from a single SAS observation

http://www.sas.com/en_us/learn/analytics-u.html

 

There are three ways to access SAS via learning mechanisms:

One downloads and runs through a VM

One can be used via AWS - charges may apply

One is through SAS Academics On Demand - Independent Learners

Occasional Contributor
Posts: 7

Re: Multiple Output Records from a single SAS observation

Thanks Reeza- will investigate the remote access opportunity and continue efforts from there

Ask a Question
Discussion stats
  • 13 replies
  • 1201 views
  • 5 likes
  • 4 in conversation