BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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.
4 REPLIES 4
Cynthia_sas
SAS Super FREQ
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
deleted_user
Not applicable
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
Cynthia_sas
SAS Super FREQ
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
deleted_user
Not applicable
thanks a ton. it has worked out fine.
Cheers

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
  • 4 replies
  • 716 views
  • 0 likes
  • 2 in conversation