How to add a column to proc sql with values from macros

[GN0001]

Hello team, 

I put together this code and it worked perfect, now I want to add a column that shows the value of the macro am passing to in the result. For example a column that shows: aaa, bbb, ccc. Regards, blue

%macro mymacro (measure=);
proc sql;
create table work.&measure. as 
select distinct
f.id
f.fname
f.lastname 
quit;
%mend;
%mymacro(measure=aaa);
%mymacro(measure=bbb);
%mymacro(measure=ccc);
run;

[PaigeMiller]

Please test your code to make sure that it works properly before providing it to us. This code does not work properly (how can you say it worked perfect?), it does not work at all. Testing your code is a good thing, and I consider it mandatory for problems like this.

After you fix these errors, you can add in

"&measure" as measure

to the list of variables.

[GN0001]

Hello, 

thank you so much, it worked.

But I need a new variable as this in proc sql and it gives errors.

Proc sql;
Create table as such
Select this, that, this count, thatcount,
New amount= thiscount - thatcount
From thistable,
Quit;

I didn’t know to post it under a new topic or here. I googled it and it says I need to alter table, which is not doable for this case because I have the proc sql in a macro that I pass various values to it.

So much appreciated.

blue 

[GN0001]

Hello Paige,

I fixed errors and everything, it ran well without issues.

Just in the result, &measure has 4 characters, one character is getting dropped. Are we not supposed to format &measure as well. Please advise me.

Regards,

Blue

[Tom · Accepted solution]

Do you mean a VARIABLE when say "column"?

How many OBSERVATIONS does the source dataset have? 

Do you want the same value for every observation?

If so then code like this will make a new dataset named NEW_DATASET from an existing dataset named OLD_DATASET that adds a new variable named NEW_VARIABLE that has the string contained in the macro variable MEASURE as its value on every observation.

create table new_dataset as 
select "&measure" as new_variable
     , *
from old_dataset
;

[GN0001]

Hello, 

thank you so very much. It took care of my query!!!
blue

[GN0001]

Hello team,

Thanks for this, I was able to complete my project. But what happens, the variable that is created should have 4 length character, but in the result, I can see that one character is dropped. I assume SAS decides about the length of &measure. How can we define the length a format for &measure?

Respectfully,

Blue

[Tom]

It should make it long enough to hold the value you passed, but trailing spaces normally are ignored when making macro variables.

If you need to tell SQL what length to use when defining a variable just add the LENGTH= option after the name.

"&measure" as new_variable length=4

So if &MEASURE is more than 4 bytes then the value will be truncated.  If it is shorter then SAS makes a normal character variable of length 4 which means the value is padded with spaces to the full length.

[Kurt_Bremser]

The code as posted can NEVER have worked, as the SELECT statement misses the mandatory FROM clause.