wanted result of proc sql union in macro variable

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 88
Accepted Solution

wanted result of proc sql union in macro variable

Hi ALl,

Can someone please help with the below query? Actually i wanted to get tow select query result in one macro variable how can i do that for eg below,

 

PROC SQL;

CREATE TABLE YTDSALES AS

SELECT TRANCODE, STORENO, SALES

FROM JANSALES

UNION

SELECT TRANCODE, STORENO,

SALES * .99

FROM FEBSALES;

QUIT;

now in above example i am creating table which consist result of two select query union value. Now instead of creating table i want that result in macro please help me how can i do that?

Regards,

Tushar J.


Accepted Solutions
Solution
‎04-24-2014 05:34 AM
Super Contributor
Posts: 644

Re: wanted result of proc sql union in macro variable

I can't test this but try this on your data.  Any errors you can't fix, be sure to post your code and the error messages from the log.

PROC SQL;

select sum(sales) into  :ytdsales

from

(

SELECT TRANCODE, STORENO, SALES

FROM JANSALES

UNION

SELECT TRANCODE, STORENO,

SALES * .99 as sales

FROM FEBSALES

)

;

QUIT;


Richard

Message was edited by: Richard Carson - moved my moniker NB TRANSCODE and STORENO can be removed from the query as they play no part in the calculation

View solution in original post


All Replies
Trusted Advisor
Posts: 1,228

Re: wanted result of proc sql union in macro variable

May be you are looink solution like this one

https://communities.sas.com/message/108195#108195

Super Contributor
Posts: 644

Re: wanted result of proc sql union in macro variable

Which variable(s) in the query do you want to put into the macro variable?  and if more than one, separated by a space?

Richard

Frequent Contributor
Posts: 88

Re: wanted result of proc sql union in macro variable

Posted in reply to RichardinOz

Hi

In the example which i mentioned above i have created YTDSALES table from that 2 select query so instead on this creating table can i create macro variable called YTDSALES using some into clause? so basically at the end all i want is result in the YTDSALES macro variable so if i do something

%put &YTDSALES;

then above statement should give me the result of union of two select query which i mentioned in my first post.

Thanks,

Tushar J

Solution
‎04-24-2014 05:34 AM
Super Contributor
Posts: 644

Re: wanted result of proc sql union in macro variable

I can't test this but try this on your data.  Any errors you can't fix, be sure to post your code and the error messages from the log.

PROC SQL;

select sum(sales) into  :ytdsales

from

(

SELECT TRANCODE, STORENO, SALES

FROM JANSALES

UNION

SELECT TRANCODE, STORENO,

SALES * .99 as sales

FROM FEBSALES

)

;

QUIT;


Richard

Message was edited by: Richard Carson - moved my moniker NB TRANSCODE and STORENO can be removed from the query as they play no part in the calculation

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 319 views
  • 0 likes
  • 3 in conversation