Appending variables to a table

Accepted Solution Solved
Reply
Contributor
Posts: 31
Accepted Solution

Appending variables to a table

Hello,

I have the following code:

/* STEP1: I have calculated the quartiles for a variable &var */

%macro grouper(&var);

proc univariate data=model.sample (keep=&var) noprint;

  var &var;

  where &var NE .;

  output out=Q

  pctlpts=34 67 100

  pctlpre=C;

run;

/* STEP2: I have selected certain variables for my table Q and calculated 3 important variables: g1_&var, g2_&var and g3_&var. */

proc sql;

  create table Q as

  select sample.siren, sample.def_post, sample.&var, Q.*, count(*) as N,          /* this expression causes errors because of the & */

  count (case when &var is missing then put (&var, 1.) end) as miss,

  (calculated miss*100/calculated N) as p_miss,

  case

  when &var=. then "miss"

  when &var LE C34 then "C1"

  when C34 < &var LE C67 then "C2"

  else "C3"

  end as groupe,

  case when &var LE C34 and &var NE . then 1 else 0 end as g1_&var,

  case when C34 < &var LE C67 then 1 else 0 end as g2_&var,

  case when C67 < &var LE C100 then 1 else 0 end as g3_&var

  from model.sample, work.Q

  order by groupe;

quit;

%mend;

/* What I want:

Say I have 20 variables named a, b, c, d, ......etc

For every one I have to submit this macro: %grouper(a); %grouper(b); ... etc


WHAT I WANT AND CAN'T HANDLE ON MY OWN:

I need to create a table that contains these variables:

a     g1_a     g2_a     g3_a     b     g1_b     g2_b     g3_b     c     g1_b     ...etc      */


Some of you will ask me what I need this table for:

Well: a, b, c, d, .... are my predictors of the default rate

I have to divide them in classes and perform a proc corr and afterwards a proc logistic on the grouped variables.


ANY IDEAS?


Accepted Solutions
Solution
‎05-20-2014 06:14 PM
Contributor
Posts: 31

Re: Appending variables to a table

You were crystal clear, but unfortunately I have to have dichotomous variables at the end and because of that your proposition requires complex intermediary steps.

It will complicate the code too much. I would prefer a more transparent solution.

I'm aware there are numerous ways of avoiding this, but often choosing the easy solution means a lot more code and worse time reaction of the pc.

anyways ... i've figured it out with some help:

/* before the macro starts */

data Q2;

     set _NULL_;

run;

/* data step to be run after each %grouper(...); */

data Q2;

     merge Q2 Q;

     by siren;

run;

Thank you everyone for your ideas.

Yes, it was a merge eventually. Not sure if this is how you pictured it, however.

View solution in original post


All Replies
Super User
Posts: 19,770

Re: Appending variables to a table

Posted in reply to loredana_cornea

A better option is to restructure your data so that you have it as follows:

Variable Value

A      1

A       1

A      1

B     2

B   2

B   3

Then you can add a BY statement to your proc univariate, a group by to your Proc SQL. 

Then reformat at the end to the table structure you want.

Contributor
Posts: 31

Re: Appending variables to a table

You didn't understand the concept.

I need variables that take the values 0 or 1. That's why I created  g1_&var, ....

If I create your type of table I have no idea how i will introduce the 0's afterwards.

I guess I'll come back and ask again ?????

If I approached your idea, my data would have to look like this:

Variable_a     value

0.5               1

0.7               1

0.9               1                              FOR GROUP=1(values < than the 34th percentile)

1.2               0

1.7               0

3.5               0

0.5               0

0.7               0

0.9               0                              FOR GROUP=2(values between P34 and P67)

1.2               1

1.7               1

3.5               0

0.5               0

0.7               0

0.9               0                              FOR GROUP=3(values over P67)

1.2               0

1.7               0

3.5               1

This will automatically rise other problems. For example I have 1.000.000 obs for variable a. That means 3*a+3*b+ .... +3*z   observations .... something monstruous will come out.

Super User
Posts: 19,770

Re: Appending variables to a table

Posted in reply to loredana_cornea

Try it.

I can only understand the information you present....

If you post sample data and output you're likely to get a more appropriate solution.

proc univariate data=model.sample noprint;

  By variable;

  var Value;

  where Value NE .;

  output out=Q

  pctlpts=34 67 100

  pctlpre=C;

run;

proc sql;

  create table Q as

  select sample.siren, sample.def_post, sample.value, Q.*, count(*) as N,          /* this expression causes errors because of the & */

  count (case when Value is missing then put (Value, 1.) end) as miss,

  (calculated miss*100/calculated N) as p_miss,

  case

  when Value=. then "miss"

  when Value LE C34 then "C1"

  when C34 < Value LE C67 then "C2"

  else "C3"

  end as groupe,

  case when Value LE C34 and &var NE . then 1 else 0 end as g1,

  case when C34 < Value LE C67 then 1 else 0 end as g2,

  case when C67 < Value LE C100 then 1 else 0 end as g3

  from model.sample as a

     join work.Q as b

on a.variable=q.variable

group by variable

  order by groupe;

quit;

Super User
Posts: 11,343

Re: Appending variables to a table

Posted in reply to loredana_cornea

Add a group variable perhaps.

Super User
Posts: 19,770

Re: Appending variables to a table

Posted in reply to loredana_cornea

Change the structure of your input data, not your output is my recommendation.

Perhaps I was unclear with that.

Solution
‎05-20-2014 06:14 PM
Contributor
Posts: 31

Re: Appending variables to a table

You were crystal clear, but unfortunately I have to have dichotomous variables at the end and because of that your proposition requires complex intermediary steps.

It will complicate the code too much. I would prefer a more transparent solution.

I'm aware there are numerous ways of avoiding this, but often choosing the easy solution means a lot more code and worse time reaction of the pc.

anyways ... i've figured it out with some help:

/* before the macro starts */

data Q2;

     set _NULL_;

run;

/* data step to be run after each %grouper(...); */

data Q2;

     merge Q2 Q;

     by siren;

run;

Thank you everyone for your ideas.

Yes, it was a merge eventually. Not sure if this is how you pictured it, however.

Super User
Posts: 19,770

Re: Appending variables to a table

Posted in reply to loredana_cornea

I'm glad you got your answer, but it would have been two proc transposes essentially assuming the code you have is everything. One before and one after Smiley Happy.

I don't see any reason you can't add the Q2 step inside your %grouper macro .

Contributor
Posts: 31

Re: Appending variables to a table

sharp eye

thnx

Super User
Posts: 19,770

Re: Appending variables to a table

Posted in reply to loredana_cornea

You can always add a merge step at  then end of your macro if its already doing what you want, but I prefer avoiding macro's if its not required here.

Contributor
Posts: 31

Re: Appending variables to a table

Normally it would work, but as you can see I'm creating the Q table that will overwrite its values every time I submit the macro.

I could change that of course but I actually have 69 potential predictors and merging 69 tables with 1000000obs each is kind of blocking my computer.

Trusted Advisor
Posts: 1,228

Re: Appending variables to a table

Posted in reply to loredana_cornea

You can put the following to avoid overwritting in Q table

proc append base=RollingQ data=Q force;run;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 315 views
  • 3 likes
  • 4 in conversation