topic Re: Check if value exists in SAS Programming

Check if value exists

adspc — Mon, 02 Mar 2020 15:51:49 GMT

Hi,

I have one main table with keys and I want to check if those keys exists in other tables. Example:

MAIN TABLE

keys
----
1
2
3

OTHERS (85 tables)

TABLE 1
key  date
---  ----------  
1    2020/01/01   

TABLE 2
key  date
---  ---------  
2    2020/01/02
3    2020/01/02

TABLE 3
key  date
---  ---------  
1    2020/01/03
3    2020/01/03

etc...

With 3 tables was ok. I did:

PROC SQL;
   CREATE TABLE WORK.QUERY_FOR_DATA AS 
   SELECT t1.A, 
          t2.B, 
          t3.B AS B1, 
          t4.B AS B2
      FROM WORK.DATA t1
           LEFT JOIN WORK.TB1 t2 ON (t1.A = t2.A)
           LEFT JOIN WORK.TB2 t3 ON (t1.A = t3.A)
           LEFT JOIN WORK.TB3 t4 ON (t1.A = t4.A);
QUIT;

RESULT
A    B           B1          B2         
---  ----------  ----------  ----------
1    2020/01/01              2020/01/03
2                2020/01/02
3                2020/01/02  2020/01/03

The problem here it's 85 tables that I need to do that and 7 millions keys! So 85 columns with 7 millions rows. And I'm getting space error.

Is there a better approach to do this? Also is it possible to make the header as that date and fill with 1 or 0. For example:

EXPECTED
A    2020/01/01  2020/01/03  2020/01/02         
---  ----------  ----------  ----------
1    1                       1
2                1
3                1           1

Thank you.

Re: Check if value exists

ballardw — Mon, 02 Mar 2020 16:28:57 GMT

@adspc wrote:

Hi,

I have one main table with keys and I want to check if those keys exists in other tables. Example:

MAIN TABLE
keys
----
1
2
3 
OTHERS (85 tables)
TABLE 1
key  date
---  ----------  
1    2020/01/01   

TABLE 2
key  date
---  ---------  
2    2020/01/02
3    2020/01/02

TABLE 3
key  date
---  ---------  
1    2020/01/03
3    2020/01/03

etc...
With 3 tables was ok. I did:
PROC SQL;
   CREATE TABLE WORK.QUERY_FOR_DATA AS 
   SELECT t1.A, 
          t2.B, 
          t3.B AS B1, 
          t4.B AS B2
      FROM WORK.DATA t1
           LEFT JOIN WORK.TB1 t2 ON (t1.A = t2.A)
           LEFT JOIN WORK.TB2 t3 ON (t1.A = t3.A)
           LEFT JOIN WORK.TB3 t4 ON (t1.A = t4.A);
QUIT;
RESULT
A    B           B1          B2         
---  ----------  ----------  ----------
1    2020/01/01              2020/01/03
2                2020/01/02
3                2020/01/02  2020/01/03
The problem here it's 85 tables that I need to do that and 7 millions keys! So 85 columns with 7 millions rows. And I'm getting space error.

Is there a better approach to do this? Also is it possible to make the header as that date and fill with 1 or 0. For example:
EXPECTED
A    2020/01/01  2020/01/03  2020/01/02         
---  ----------  ----------  ----------
1    1                       1
2                1
3                1           1
Thank you.

I would suggest supplying some reasoning behind why you need to know "if those keys exist in other tables".

Bringing lots of other variables doesn't really help answer the base question and combining values from multiple tables across as your are doing likely makes things much more complicated.

If the only other variable of interest is always called date I would suggest making temp data sets that contained
Key Table Date

Which could likely be created using call execute with dictionary.tables to create the code to make them.

Then if needed append all of the temp tables and write a report.

Re: Check if value exists

PaigeMiller — Mon, 02 Mar 2020 18:14:52 GMT

I agree with @ballardw that providing more clear logic and possibly a different approach could get you to the final destination.

You final output tables are not really in a format that I would advise, as they don't work well in SAS; and so I would advise you have one very long data set rather than the wider data sets you are asking for. Variables Key Table Date — oh wait, @ballardw already said that. If you absolutely need dates as the columns as in your example, that can be done via PROC REPORT, don't assume that you have to have a SAS data set organized that way, you want a long SAS data set, not a wide SAS data set.

I also think SQL is completely the wrong tool here, as handling 85 tables will be a lot of coding. Something more along these lines would be better, in my opinion:

data want;
    set table:;
run;

Not a lot of coding at all.