BookmarkSubscribeRSS Feed
GN0001
Barite | Level 11
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

 

Blue Blue
8 REPLIES 8
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
GN0001
Barite | Level 11

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

Blue Blue
Tom
Super User Tom
Super User

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
Barite | Level 11
Hello,
Thanks for response.
How can we do it without using assignment statement?
Is that possible?
Regards
Blue
Blue Blue
Quentin
Super User

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.

The Boston Area SAS Users Group is hosting free webinars!
Next webinar will be in January 2025. Until then, check out our archives: https://www.basug.org/videos. And be sure to subscribe to our our email list.
GN0001
Barite | Level 11

@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

Blue Blue
Quentin
Super User

@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?  

The Boston Area SAS Users Group is hosting free webinars!
Next webinar will be in January 2025. Until then, check out our archives: https://www.basug.org/videos. And be sure to subscribe to our our email list.
Kurt_Bremser
Super User

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)

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 8 replies
  • 1490 views
  • 6 likes
  • 5 in conversation