how to add a new variable in an assignment statement

[GN0001]

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

Hello team,

I appreciate your input!

I googled and I saw that I need to do alter table. Since I have a macro to pass values to it, that makes it very difficult. Are there any solution for this?

Regards,

blue blue

[PaigeMiller]

Its not clear to me how your subject line relates to the rest of the problem. You may not need to do an ALTER, and if you do need to do an ALTER, macros are no more difficult with ALTER than with any other statement.

But in order for us to help, a much more clear description of the problem is needed. Don't talk in terms of how to do this with ALTER, don't talk in terms of macros, just describe what needs to be done.

[GN0001]

Hello,

I need to add one assignment statement in the proc sql.

New variable = thisvariable - that variable.

Please let me know if this is clear.

Respectfully,

blue

[Tom]

First make sure to use actual variable names.  You cannot assign values to both NEW and VARIABLE in one assignment statement.  And you would need an operator or something between the variables THAT and VARIABLE.

You don't use ASSIGNMENT statements in SQL.  You do that in DATA steps.  Perhaps your whole program will be easier if you just use a data step instead of SQL?  

data want;
  set have;
  New_variable = thisvariable - that_variable.
run;

In SQL you SELECT values.  If the value being selected is an expression (or if you want to use a new name) then add that AFTER the variable.

proc sql;
create table want as
  select *,thisvariable - that_variable as New_variable
  from have
;
quit;

[GN0001]

Hello,
Thanks for response.
How can we do it without using assignment statement?
Is that possible?
Regards
Blue

[Quentin]

Please read Tom's answer again. As he explained, SQL doesn't have an assignment statement.  He showed examples of creating a new variable via DATA step assignment statement and on a SQL select clause.

[GN0001]

---

@Quentin wrote:

Please read Tom's answer again. As he explained, SQL doesn't have an assignment statement.  He showed examples of creating a new variable via DATA step assignment statement and on a SQL select clause.

---

Yes, I used in both cases, but how about if we don't want to use it in assignment statement.

Regards,

Blue

[Quentin]

---

@GN0001 wrote:

---

@Quentin wrote:

Please read Tom's answer again. As he explained, SQL doesn't have an assignment statement.  He showed examples of creating a new variable via DATA step assignment statement and on a SQL select clause.

---

Yes, I used in both cases, but how about if we don't want to use it in assignment statement.

Regards,

Blue

---

I'm confused.  Your question how to add a variable with an assignment statement.  Are you now asking a new question?  Can you write the full question?  

[Kurt_Bremser]

To add a new variable and give it a value in SQL, you have to use the

thiscount - thatcount as new_amount

construct, period.

If you want to store a value into a macro variable, use SELECT INTO without creating a table and without creating a new SQL variable (no AS needed)