Passing a value from Netezza back to SAS using a macro variable.

[Tater_Salad]

HI, we're using SAS 7.13 HF1 (7.100.3.5419) (64-bit)

I'm currently looking at a post that shows how to pass a value from SAS to a database that your connecting in to. Here is the example below. You can see they take the Macro variable StartDate and pass it into Teradata to be used in the query.

%let StartDate = 2016/01/01;

proc sql;
   connect to teradata 
    (BULKLOAD=YES MODE=TERADATA user=&user. Password=&passwd.);

   CREATE TABLE WorkTest.test AS
   select * from connection to teradata
   ( 
		SELECT 
			TOP 10 *      
		FROM SomeCalendarData
		WHERE SomeDate = %bquote('&StartDate.');
   ); 
quit;

 I want to go the other way.

How can I read a value from a similar query, only my DB is Netezza, and somehow pass it to a macro variable in SAS? 

Thanks!

[JBailey]

Hi @Tater_Salad

I don't have SAS 7 available (it turns out that 7 is the version of SAS Enterprise Guide - I had no idea) but here is a SAS 9.4 and Oracle example that shows how to do it. Netezza should be very similar. The CONNECT USING statement is new (and awesome). CONNECT USING isn't available in SAS 7 - you will want to use the CONNECT statement that you used in the code sample you provided.

I have updated the example based on a comment by @SuryaKiran

libname myora oracle path=myora user=myuser password=mypassword;

data myora.cars;
   set sashelp.cars;
run;

/* This is a slow way to do it */
proc sql;
   connect using myora;
   select count(*) into :mycount from connection to myora
      (select * from cars);
quit;

/* This is a faster way to do it and the results will be the same */

proc sql;
   connect using myora;
   select this_count into :mycount from connection to myora
      (select count(*) as this_count from cars);
quit;

%put "mycount=&mycount"; 

The output of the %put statement is...

8    %put "mycount=&mycount";
"mycount=     428"

To reference the actual SAS macro variable use only &mycount.

Hope that helps,

Jeff

[Tater_Salad]

Well for SAS 7 people do you or anyone know how I can do this?

Thanks!

[JBailey]

Hi @Tater_Salad

This should work with SAS 7 if you don't use the CONNECT USING form of the CONNECT statement. Use the CONNECT statement from your example.

Best wishes,

Jeff

[Tater_Salad]

Really quick I did an acid test by pasting your code section into my SAS Enterprise Manager figuring if your new keywords (connect using)  were accepted by SAS 7 the editor wold highlight them in blue indicating reserved words and therefor compatible. If the "connect using" highlighted in red then they would be rejected by the editor as unknown , unsupported or a syntax error. I'm sorry to say the "connect" word highlighted in red. 

Not sure what an alternative would be to this.

Thanks!

[Tater_Salad]

I'll give it a shot.

[SASKiwi]

"we're using SAS 7.13 HF1 (7.100.3.5419) (64-bit)" - this is your Enterprise Guide version not SAS. 

To get your SAS version  run this: %put &sysvlong;

[Tater_Salad]

Well what do you know:

9.04.01M4P110916

Can you guys tell I'm new at this?

OK Back to square 1. I'm going to modify the solution above to get it to work in my editor.

I'll let you know...

[Tater_Salad]

Well what do you know:

"9.04.01M4P110916"

Can you guys tell I'm new at this?

OK Back to square 1. I'm going to modify the solution above to get it to work in my editor.

I'll let you know...

[Tater_Salad]

OK it works but I have another question. Here's my code segment for this at the bottom.

In the Select statements all I can print out are the row counts. I'm trying to use "trim" because I'm getting answers like:

"                21"

Also I'm trying to use "limit 1" to limit the output to the first row.  It won't take it. So I'm trying to use functions like MAX. Also I'm using "group by" to get the output to one row and none of them seem to work is there anything I can do to just get data from the first row of the table. 

What works here?

%let us_mkt = server='server.net' database='IDP_PRD' port=5480 user=&db2_idx. password=&win_pwdx.;

libname ME netezza &us_mkt. schema=USER_ME;

data ME.USER_ME.SALES_TBL;
 set ME.SALES_TBL;
run;

/* This is a slow way to do it */

proc sql;

 connect using ME;

 select * into :mycount1 from connection to ME
 (select trim(*) from USER_ME.SALES_TBL_2);
quit;

%put "mycount1=&mycount1";

/* This is a faster way to do it and the results will be the same */
proc sql;

 connect using ME;

 select this_count into :mycount2 from connection to ME
 (select count(*) as this_count from USER_ME.SALES_TBL);
quit;

%put "mycount2=&mycount2";

[Tom · Accepted solution]

You seem to multiple questions. Most of which seem to be related with creating macro variables using PROC SQL.

The spaces in the macro variable are controlled (mainly) by the INTO clause.  If you taking just one value then use the TRIMMED keyword.

select XXX into :mycount TRIMMED
from ....

If you want to select multiple values then use the SEPARATED BY keyword of the INTO clause.

So if you had a character grouping variable you might use something like this to make two macro variables. One with the list of groups and the other with the list of counts.  You can save the value of SQLOBS to see how many groups were found.

select 
    quote(trim(groupvar))
  , nobs format=32.
  into :group_list separated by ','
     , :count_list separated by ','
  from connection to me
(select groupvar,count(*) as nobs from ME.TABLE group by 1 order by 1)
;

%let n_groups=&sqlobs;

[Tom]

SAS version 7?

Nobody used SAS 7 when it was actually available. I am pretty sure you are not using it now.

[Tater_Salad]

Hi Tom,

Right we're version 9 now. Take a look at my above response. Any ideas?

Thanks!