The SAS Output Delivery System and reporting techniques

output rows based on lookup table

Reply
N/A
Posts: 0

output rows based on lookup table

how do i restrict the number of obs from a table based on the value in another table.

eg :
table A is the source table.
table B is the lookup table which has the info regarding the number of obs to extract from the source table.
SAS Super FREQ
Posts: 8,740

Re: output rows based on lookup table

Hi:
Do you mean table b contains the values for which rows should come out of table a? Like this:
[pre]
table a table b
123 fred 123
456 george 567
567 anne
789 mary
[/pre]
OR that table b contains the number of rows that should be selected, possibly to get the first n rows, from table a? Like this:
[pre]
table a table b
123 fred 3
456 george
567 anne
789 mary [/pre]

I could interpret your question either way. This may not actually be an ODS question, though. Are you using ODS OUTPUT to create your output tables?
cynthia
N/A
Posts: 0

Re: output rows based on lookup table

Let me simplify :

table A has
ID Name
1 ALT
2 ALT
3 ALT
4 ALT
5 Cy
5 Cy

table B has
Name Total_Records
ALT 2
Cy 1


Output has to contain 2 records for ALT and 1 record for cy : (as under)
1 ALT
2 ALT
3 CY
SAS Super FREQ
Posts: 8,740

Re: output rows based on lookup table

It seems to be a merge task to me and requires a data step program. Based on your example (and assuming that you want the first xx number of rows), the code below may be close to what you want:
[pre]
** Both data sets must be sorted by name;
** and whatever other variables you want;
proc sort data=a; by Name; run;
proc sort data=b; by Name; run;

** Merge by Name and use a counter variable (CNTR) to;
** keep track of which rows to output.;
** Make 3 data sets: ;
** Matches = selected rows in A based on Name and total_records from B;
** Excluded = "extra" rows from A or Rows in A not found in B;
** Errors = rows in B that were not found in A;
data matches excluded errors;
retain cntr;
merge a(in=ina)
b(in=inb); by name;
if first.name then cntr = 0;
if ina and inb then do;
cntr + 1;
if cntr le total_records then output matches;
else if cntr gt total_records then output excluded;
end;
else if ina and not inb then output excluded;
else if inb and not ina then output errors;
run;

** Titles indicate logic how rows got into the file;
proc print data=matches noobs;
title 'Rows in A and B, Number of Rows based on table b';
run;

proc print data=excluded noobs;
title 'Rows in A not requested in B';
title2 'Either they had more rows in A than requested OR';
title3 'They were listed in A, but not requested in B';
run;

proc print data=errors noobs;
title 'Rows in B not found in A';
run;
[/pre]
There's one condition yet to test, depending on how you want to handle it and that's what you want to do if they request 3 rows in table b, but there are only 2 rows for that name in table a. This may not be a condition that happens in your data, but one way to deal with it would be to go ahead and output the rows, but change the logic of the "matches" IF statement as shown below which puts a note in the log to check the name: [pre]

if ina and inb then do;
cntr + 1;
if last.name and cntr le total_records then do;
putlog "*** Check: " name= ' **' cntr= ' ** ' total_records=;
end;
if cntr le total_records then output matches;
else if cntr gt total_records then output excluded;
end;
[/pre]

If your data are more complicated, then your program logic will need to get more complicated. Your best bet would be to talk to Tech Support for more help with this question.
cynthia
N/A
Posts: 0

Re: output rows based on lookup table

thanks a ton. it has worked out fine.
Cheers
Ask a Question
Discussion stats
  • 4 replies
  • 152 views
  • 0 likes
  • 2 in conversation