Creating a new data table using old data table

[deleted_user]

Hi,

I'm new to SAS and I need some programming help. I am doing a project on
poverty and social mobility. I basically have two datasets:
- POVTBLFAM
- NEWPEOPLE

POVTBLFAM is filled with micro-data on person level. NEWPEOPLE is an empty
dataset, which I will fill up using POVTBLFAM. To make this easier
I've created the simple macro:

%macro ADDFORECASTEDROW(v1,v2,v3);
proc sql;
insert into NEWPEOPLE
values (&v1,&v2,&v3);
select * from NEWPEOPLE;
%mend ADDFORECASTEDROW;

Suppose that both POVTBLFAM and NEWPEOPLE contains the columns (id, job,
weight). Then I want to do something like this:

%macro CREATENEWPEOPLE();
data POVTBLFAM;
set POVTBLFAM;
if id > 0 and job = 1 then
%do;
ADDFORECASTEDROW(id, 0, 0.2);
ADDFORECASTEDROW(id, 1, 0.8);
%end;
run;
if id > 0 and job = 0 then
%do;
ADDFORECASTEDROW(id, 0, 0.5);
ADDFORECASTEDROW(id, 1, 0.5);
%end;
run;
%mend CREATENEWPEOPLE;

In other words, for every entry in my POVTBLFAM, i want to create 2 new
entries based on these values... Well actually I want to create 96 new
entries for every entry in POVTBLFAM, but I think that is just a question of
scaling up my solution - which I can't get to work with just two entries.

With the above code SAS gives me the following errors:
- ERROR 180-322: Statement is not valid or it is used out of proper order.
- ERROR 395-185: Opening parenthesis for SELECT/WHEN expression is missing.
- ERROR 200-322: The symbol is not recognized and will be ignored.
- ERROR 76-322: Syntax error, statement will be ignored.

I've been reading online that my errors might be because the macro-statement
simply plugs in its own code at the very place I write my macro-name, and that
putting proc sql inside my data statement is illegal. But then: how can I create a
new data set using the values from my old one?

Any help would greatly be appreciated 🙂

Regards Julian.

[LinusH]

Proc SQL cannot execute within a data step, which this code will try to do (by calling the macro from within a if-then-do-block).

/Linus

[Reeza]

I don't think you need a macro for this...I think you need a data step, possibly an array (since you say 96 outputs) and control the output with output statements.

Could you post what you want your original data looks like and what you'd like as a result?

[deleted_user]

Well my data is basically just row-based. Every single row represents a household, for example: id, earners, children, gender of household head, job status, etc...

I want to go through the dataset, row by row, and for each row create 96 new rows in a different dataset, as transformation of the row in first dataset, but only using this row...

It sounds like you are right Reeza. How can I do this? Do you have any examples?

[Reeza]

You have a good example above, but I'll add in a brief example of the do loop since you don't want to change type output manually 96 times.

data have;
set sashelp.class;
run;

data want;
set have;

do i=1 to 96;
job=i; sample_weight=i/96; output;
end;
run;

[deleted_user]

Thanks! I'm almost there. Playing around with the code I ended up with:

%macro Forecast_Job_Status(v1,v2,v3);
%let x=0.25;

if &v1 = '1' then x=0.1;

&x;
%mend Forecast_Job_Status;

data have;
set sashelp.class;
run;

data want;
set have;
%let p_age=Age;
%let p_sex=Sex;
%let p_height=Height;

do i=1 to 2;
do j=1 to 2;
do k=1 to 2;
do l=1 to 3;
job=%Forecast_Job_Status(&p_age, &p_sex, &p_height);
sample_weight=i/96;
output;
end;
end;
end;
end;
run;


As you can see, I want to use the variables inside a macro and there determine the new values. However this yelds me a lot of erros again, and I can't see what's wrong:

ERROR 388-185: Expecting an arithmetic operator.

ERROR 202-322: The option or parameter is not recognized and will be ignored.

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, -, /, ;, <,
<=, <>, =, >, ><, >=, AND, EQ, GE, GT, IN, LE, LT, MAX, MIN, NE, NG, NL, NOTIN,
OR, [, ^=, {, |, ||, ~=.

ERROR 180-322: Statement is not valid or it is used out of proper order.

[Reeza]

Couple of things:

1. You cannot assign a macro variable (your %let statements) and then use it again in the same dataset.(not easily)
2. Why assign macro variables for variables in a dataset when you could just use them in the call statement for the forecast.
3. Why call a macro at all and not include that code nested in the do loop.
4. Why do you have i/j/k/l when they don't appear to be used?

I don't know enough about what you're trying to achieve beyond this :(. You could put what the data looks like and what you need, if you want more help.

Cheers,
Reeza

[deleted_user]

Cynthia@sas:

The &x was supposed to return the value of x.

Reeza;

1. It was not my intention to make a macro variable, just to have a temporary variable that I can calculate on.... The problem is I actually want to use a macro, because otherwise the code inside my loop will be huge. The idea was just to pass some parameters from my data to the macro, and then the macro would return the relevant calculation.

4. My i,j,k,l are just there to simply my loop. They all represent a different parameter I am altering. This is how I get to 96 new rows from a single row (2x2x2x3x4).

2. How can I use the same variable that I am setting in a call statement? I think chang demonstrated this, but only in if-statements. I want to use it as a value for setting the new variable. Or can I simply do this?

...

So basically I have two questions:
- can I use a macro (or otherwise) shorten my long code in the nested loop?
- when creating my new rows, how I can use my old variable to calculate my new one directly, without if-statements?

Thank you for taking your time. I hope I don't seem to thick headed.

- Julian.

[deleted_user]

I don't know if it makes it more clear, but here is some of the code I am working on:


PROC SQL;
CREATE TABLE POVTBLFAM_Updated AS
SELECT d4, d6, d27, d29, married, hpsex, hpage, hpeduc, hpdisabl, hpumas, hpethnat, spsex, spage, speduc, spdisabl, spumas, spethnat, inpoverty, hrawage, srawage,
/* Calculation */
(CASE
WHEN d4 = 1 AND d27 = 0 THEN 1.041284404
WHEN d4 - d27 = 1 THEN 1.166197183
WHEN d4 = 2 AND d27 < 1 AND hrawage < 65 AND srawage < 65 THEN 1.102272727
WHEN d4 = 2 AND d27 < 1 AND hrawage > 64 OR srawage > 64 THEN 0.962962963
WHEN d4 - d27 = 2 AND d27 = 1 THEN 1
WHEN d4 - d27 = 2 AND d27 = 2 THEN 0.79245283
WHEN d4 - d27 = 2 AND d27 > 2 THEN 1.205882353
WHEN d4 > 2 AND d27 = 0 THEN 0.716216216
WHEN d4 > 2 AND d27 > 0 THEN 1.75
ELSE 0
END) AS HWEIGHT
FROM POVTBLFAM;
QUIT;


Now it's from "POVTBLFAM" that I want to create a new table. To start with I'd only need 26 rows per old row. So my code continues like this:


proc sql;
create table FORTBL
like POVTBLFAM_Updated;

Then I'd want to create a few macros to shorten calculations later:
%macro

Prob_Having_A_Job(v1,v2,v3,v4,v5);
if v1 = 0 then x = 0.1;
if v1 = 1 then x = 0.9;
&x;
%mend Prob_Having_A_Job;


Prob_Having_A_Child(v1,v2,v3,v4,v5);
if v1 = 0 then x = 0.1;
if v1 = 1 then x = 0.9;
&x;
%mend Prob_Having_A_Child;


And then I'd use these to make my new dataset "FORTBL"... Simplyfing it somewhat, and using things I am not entirely sure about:


data want;
set have;
do i=1 to 2;
do j=1 to 2;
do k=1 to 2;
do l=1 to 3;
if i = 1 then do;
hpumas=1;
sample_weight=Prob_Having_A_Job(hpsex, hpage, hpeduc, hpdisabl, hpumas);
output;
end;

if i = 2 then do;
hpumas=1;
sample_weight=1-Prob_Having_A_Job(hpsex, hpage, hpeduc, hpdisabl, hpumas);
output;
end;

end;
end;
end;
end;
run;

[Reeza]

It looks to me like you're creating a table then assigning different probabilities based on some demographic statuses. I'd consider separating the process.

Ie generate the table with the different combinations of probabilities you'd like and then fill in the probabilities using either a lookup, a format, a merge or a hashtable.

It also looks like you're trying to define your own 'function' but SAS does not do that as far as I know.

You can see this doc http://www.lexjansen.com/phuse/2006/cs/cs06.pdf
if you want to try and go down the route of defining your own function. That's beyond me.

[Cynthia_sas]

Julian:
That is not how you "return" macro variables. Macro variables are stored in global and local symbol tables. If you know what the scope of your macro variable is, or you guarantee the scope of your macro variable with a %GLOBAL statement, then the macro variable will be written to the correct table and will be available for you to use appropriately. A SAS Macro is not the same thing as a function call that "returns" a value (although you could write a SAS Macro program to serve in that way). However, I am not convinced that this is really what you want to do.

Have you done an MPRINT or a SYMBOLGEN on your program??? I think you will be surprised by what you see. The way to turn on these options is:
[pre]
options symbolgen mprint mlogic;
[/pre]

If you just have &X; as shown in the Macro program, then the value of &X will get sent to the compiler, as though you had typed it into your program. I do not believe this is what you want. This is a particularly useful section on debugging macro programs with an example of using a macro call to create a variable value (very similar to what you want to do, only without the loop):
http://support.sas.com/documentation/cdl/en/mcrolref/61885/HTML/default/viewer.htm#a001066200.htm (I'd recommend that you study this program and understand how it's working before going too far down the loop road.)

And this documentation topic discusses the scope of macro variables:
http://support.sas.com/documentation/cdl/en/mcrolref/61885/HTML/default/viewer.htm#a002047080.htm

One of the most important things to remember is that by the time your code statements get to the SAS compiler, there are NO macro variable triggers (%macpgm or &macvar) left in your code. All of these references -- EVERYTHING --has been resolved.

It also seems to me that there is still something fuzzy about your requirements and how you plan to use &X (the macro variable) and how you plan to use X (the data step variable).

cynthia

[deleted_user]

Thanks Reeza and Cynthia, I finally managed to finish the program.

I think lookup was the right way to go, but simply using the data-step as you suggested Reeza worked out really fine :). I also realize why I cannot use macros the way I wanted to use them.. It's a hard shift coming from an OOP to SAS.

- Julian.

[Cynthia_sas]

Hi:
What do you anticipate will be generated as a result of the highlighted statement:
[pre]
%macro Forecast_Job_Status(v1,v2,v3);
%let x=0.25;

if &v1 = '1' then x=0.1;

&x; <--what do you think this statement will do??????

%mend Forecast_Job_Status;
[/pre]

Have you turned on SYMBOLGEN and MPRINT to see what code statements are being generated by the simple reference to &x??? Are you possibly confused about the fact that there is a difference between the DATA step variable X and the Macro variable &X??? What do you NEED/WANT the single &X; in the macro program to do?????

cynthia

[chang_y_chung_hotmail_com]

Even though it is possible to execute proc and data steps within another data step, (an example is found here), there are much easier ways, for instance:

   /* test data */

   data p;

     input id job weight;

   cards;

   1 1 10

   2 0 20

   3 1 30

   ;

   run;

   /* create dataset n from p */

   data n;

      set p;

      if job = 1 then do;

         job = 0;

         weight = 0.2;

         output;

         job = 1;

         weight = 0.8;

         output;

      end; else if job = 0 then do;

         job = 0;

         weight = 0.5;

         output;

         job = 1;

         weight = 0.5;

         output;

      end; 

   run;

   /* check */

   proc print data=n;

   run;

   /* on lst

   Obs    id    job    weight

    1      1     0       0.2

    2      1     1       0.8

    3      2     0       0.5

    4      2     1       0.5

    5      3     0       0.2

    6      3     1       0.8

   */