how to code parameter file

Reply
New Contributor
Posts: 4

how to code parameter file

I am writing a code in SAS.... 

 

 

Proc sql;

         create table temp as

         select 

                  (case

                          when case1 then "parameter1"

                          when case2 then "parameter2"

                           else ""

                   end) as newvar

           from mytable t1;

quit;

 

I want to have an excel file with values of parameters:

 

parameter1 0

parameter2 1

 

 

I want to load the parameter file as a separate table in EG. Pass the parameter values to "proc sql" and replace it with actual values. The idea is that I can code all cases at once with many parameter depending on different cases. Play with parameter values in a separate excel file. As I run the code, I get new tables.

 

The result of the above will be;

 

myvar 

0

1

 

But if I rerun the code with a different parameter file:

parameter1 5

parameter2 10

 

the result will be 

myvar

5

10

 

Thanks!

Grand Advisor
Posts: 10,210

Re: how to code parameter file

Can you provide a concrete and non-trivial example of the start and end of the entire process? I ask because recoding variables or creating new variables the way you propose feels like it is very likely possible to approach in a number of different manners. If your "case1" or "case2" are single variable and sets of values then formats or informats may work. If more complicated then possibly a hash.

 

One things is that you appear to be going through the same data set many times and then you'll have a task of sticking things back together later.

Grand Advisor
Posts: 17,325

Re: how to code parameter file

What does parameter1/parameter2 look like?

Are they text? Numeric? A rule/logic?  

 

Otherwise this seems like a straightforward look up, which could be automated with a join or format. 

New Contributor
Posts: 4

Re: how to code parameter file

Concrete example:

 

 

 

---- input data -------------

 

data temps;
input key gender $ age salary tenure;
datalines;
01 Male 25 60000 2
02 Female 40 100000 10
03 Female 50 120000 20
04 Male 35 80000 15
;

 

---------- run sql ------------------------

Proc sql;

         create table temp as

         select key, 

                  (case

                          when Gender = "Female" then "g1"

                          when Gender = "Male" then "g2"

                           else ""

                   end) as scoreG,

                  (case

                          when age <= 30 then "a1"

                          when age > 30 then "a2"

                           else ""

                   end) as scoreA

               (case

                          when salary <= 75000 then "s1"

                          when salary > 75000 then "s2"

                           else ""

                   end) as scoreS

               (case

                          when tenure <= 10 then "t1"

                          when tenure > 10 then "t2"

                           else ""

                   end) as scoreT

           from mytable t1;

quit;

 

--------------  

 

So the original data is:

 

Original table (temps):

 

key   gender       age     salary      tenure

01      Male         25        60000        2   

02      Female     40      100000      10

03      Female     50      120000      20

04      Male         35        80000      15

 

and the result of running sql code is:

 

temps1:

 

key   scoreG   scoreA   scoreS   scoreT

1           g2          a1         s1          t1
2           g1          a2         s2          t1
3           g1          a2         s2          t2
4           g2          a2         s2          t2

 

------------------------------------------------

 

 

I want to have an excel file with values of parameters:

 

Lookup table:

 

f1  0

f2  1

a1  0

a2  1

s1  0

s2  1

 

t1   0

t2   1

 

 

Upload it to EG and replace g1, g2, a1, a2, s1, s2, t1, t2 with the numerical values in the lookup table. Once I substitute the parameter labels in temps1 with the actual numerical values the table will look like:

 

 

key   scoreG   scoreA   scoreS   scoreT

1           1          0            0            0
2           0          1            1            0
3           0          1            1            1
4           1          1            1            1

 

 

 

 

The idea is that I code all cases once. Have a separate lookup table, play around with different values and be able run the sql code without changing and get the new values from lookup table reflected in the final table.

 

I am looking for a code that implements this.

 

Thanks in advance!

 

Grand Advisor
Posts: 17,325

Re: how to code parameter file

I think formats are your best bet. 

 

This paper is a good intro since it covers a wide variety of use cases. Your 'want' file may be a bit too basic though, you need at least a way to define which variables are applied to each value. Unless you can all values in your lookup are unique. 

 

http://www2.sas.com/proceedings/sugi30/001-30.pdf

 

Examples 1/3/4 are relevant to your question

 

And creating formats from datasets is covered more here:

http://www2.sas.com/proceedings/forum2007/068-2007.pdf

 

 

Grand Advisor
Posts: 17,325

Re: how to code parameter file

In your current coding system all things that end in 1 get coded to 0 and things that end in 2 are coded to 1, could you utilize that logic instead of your lookup table? I'm not sure how representative it is of your actual situation. 

 

 

New Contributor
Posts: 4

Re: how to code parameter file

This is a simple illustrative example of what I want to achieve. I have a huge sql query with many variables involved and each variable values will have multiple cases. The idea is to code the cases and give a label to the values for each case. There will be an independent lookup table with values for each case. So, there is flexibility of changing a single value in the lookup table and running the code again to reflect changes. This is fairly simple in general coding such as c, c++, fortran, etc. We can define some variables in the code in the beginning. Use  those variables in the code. Compile the code. While running the code, the executable reads the values from a file and gives the results. I am trying to achieve this task in SAS.

Grand Advisor
Posts: 10,210

Re: how to code parameter file

From what I am seeing I do not think that you even need to add variables at all. Please see this example code:

data have;
   input key   gender  $     age     salary      tenure;
datalines;
01      Male         25        60000        2   
02      Female     40      100000      10
03      Female     50      120000      20
04      Male         35        80000      15
;
run;

proc format library=work;
value $scoreG
'Female' = 'g1'
'Male' = 'g2'
;
value ScoreA
low - 30 = 'a1'
30 <- high = 'a2'
;
value ScoreS
low - 75000 = 's1'
75000 <- high = 's2'
;
value ScoreT
low - 10 = 't1'
10  <- high= 't2'
;
run;

proc print data=have noobs label;
   var key gender age salary tenure;
   format gender $scoreG. age scoreA. salary ScoreS. tenure scoret.;
   label
      gender ='scoreG'
      age    ='scoreA'
      salary ='scoreS'
      tenure ='scoreT'
   ;
run;

Note the use of LABEL to change the variable description at need and the custom formats to display specified values.

 

Your example cases are very simple. I have formats that recode 100's of codes from things like '01234' to 'City X branch on main street'.

Proc format supports creating formats from a data set of the correct structure so if you already have that information well organized then that may be an option.

Another advantage is that if you have multiple values of similar meaning you can apply the format to multiple variables such as AgeAtGraduation, AgeAtHire, AgeAtDeparture.

 

SAS will honor the formatted values for almost every procedure. Please see this brief example:

data have;
   input key   gender  $     age     salary      tenure;
datalines;
01      Male         25        60000        2   
02      Female     40      100000      10
03      Female     50      120000      20
04      Male         35        80000      15
;
run;

proc format library=work;
value $scoreG
'Female' = 'g1'
'Male' = 'g2'
;
value ScoreA
low - 30 = 'a1'
30 <- high = 'a2'
;
value ScoreS
low - 75000 = 's1'
75000 <- high = 's2'
;
value ScoreT
low - 10 = 't1'
10  <- high= 't2'
;
run;

proc print data=have noobs label;
   var key gender age salary tenure;
   format gender $scoreG. age scoreA. salary ScoreS. tenure scoret.;
   label
      gender ='scoreG'
      age    ='scoreA'
      salary ='scoreS'
      tenure ='scoreT'
   ;
run;

This format approach becomes very powerful if some asks: how does the analysis change if we look at age in under 25, 25-40 and 40 plus groups? Create the format and re-run the analysis with the new format. Not additional variable needed.

 

Ask a Question
Discussion stats
  • 7 replies
  • 495 views
  • 1 like
  • 3 in conversation