topic Re: Extract dummies variable in SAS Programming

Extract dummies variable

rxhmx — Fri, 15 Nov 2019 09:25:27 GMT

Dear all,

I have a project in which I have to check automatically the evolution of some variables each month

For instance, I have to chech the frequency of each modality for a variable in the table n-1 compared to the table n

These variables are dummies and can have until 100 modalities.

The databases are really big and I'm looking for a way to extract from each table the variables with categorical data (there is more than 300 variables) and merge them two by two

Does someone know if it's possible ? I tried to use proc contents but I did'nt help me

Thank you in advance ! 🙂

Re: Extract dummies variable

ChrisNZ — Fri, 15 Nov 2019 09:39:41 GMT

Welcome @rxhmx !

So you need to compare the list of unique values for 300 variables, from month to month?

Or the does the list of values stay constant, and only the frequency of each value changes?

In any case, and more importantly, how do you want to convey or to report changes across 100 values for 300 variables?

Assuming you only compare VAR1 to VAR1, and VAR2 to VAR2, that's still possibly 300,000 comparisons.

This cannot be read by a human, How should the changes be shown?

Re: Extract dummies variable

rxhmx — Fri, 15 Nov 2019 09:48:31 GMT

Hi Chris ! Thank you for your answer

In these 300 variables there is maybe 15 or 20 categorical variables, the number of values of these 15 variables can increase or decrease.

I want to compare them two by two using proc freq for instance

I think the changes can be readable if it only concerns a few values

I don't know if it is clear (english is not my native langage)

To sum up I want someting like "For VAR 1 from table 1 the frequency of blue is 20% and for var 1 from table 2 the frequency of "blue" is 21%"

Re: Extract dummies variable

ChrisNZ — Fri, 15 Nov 2019 11:01:18 GMT

Why not a proc summary on each table and then merge the results and compare.

proc summary data=TAB1; 
  class VAR1 VAR2;
  ways 1;
  output out=SUM1;
 run;

Re: Extract dummies variable

ChrisNZ — Fri, 15 Nov 2019 20:12:59 GMT

Or even no merge and proc compare.

Re: Extract dummies variable

Patrick — Fri, 15 Nov 2019 20:20:57 GMT

@rxhmx

You write "The databases are really big"

Can you please quantify "big"?

What does "database" mean? SAS tables or actually tables in a database like SQL Server or Oracle?

Re: Extract dummies variable

PaigeMiller — Fri, 15 Nov 2019 20:34:47 GMT

@rxhmx wrote:

Dear all,

I have a project in which I have to check automatically the evolution of some variables each month

For instance, I have to chech the frequency of each modality for a variable in the table n-1 compared to the table n

These variables are dummies and can have until 100 modalities.

The databases are really big and I'm looking for a way to extract from each table the variables with categorical data (there is more than 300 variables) and merge them two by two

Sure this is possible, I think you would probably need to perform PROC FREQ on each data table, then a macro of some sort to loop through the different variables and compare the results of the PROC FREQ across the two tables. Although first I would investigate if PROC COMPARE does enough to meet your needs.

You phrase this problem in terms of "dummies", but if I am understanding you properly, there is no need for dummy variables at all.