Macro List in sql with numeric value more than 8

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Macro List in sql with numeric value more than 8

Hi all,

 

I am wondering if you could help on the following.

 

I am using the following to create a macro variable which contains a list of IDs:

PROC SQL NOPRINT;

SELECT CUST_ID INTO : ID_LIST

SEPARATED BY ','

FROM CUSTOMER_TABLE;

QUIT;

%put &ID_LIST;

 

And then I am using this ID_LIST to extract data from the SALES_TABLE with the following:

PROC SQL; CREATE TABLE SALES_INFO AS SELECT * FROM SALES_TABLE

WHERE SALES_CUST_ID in (&ID_LIST);

QUIT;

 

In general the above works fine. The issue is that some of the CUST_ID have more than 8 numbers. For example, when I try to find the CUST_ID: 12345678 iT works. However, when I try to find the CUST_ID: 123456789 it does not work. 

 

When I use the: %put &ID_LIST; I can see that those CUST_ID which have more than 8 numbers are shown as: eg. 1.0968E8, and that's why I cannot find those CUST_ID in the SALES_TABLE. 

 

I hope it makes sense.

 

Is there any way to deal with that issue? 

 

Any comments and/or suggestions would be greatly appreciated.

 

Thank you.

 

Regards,

Zatere

 

 


Accepted Solutions
Solution
‎12-08-2016 08:06 AM
Frequent Contributor
Posts: 130

Re: Macro List in sql with numeric value more than 8

Hi Zatere.

 

The problem is that the default format for numeric variables reformats your 9 digit number to fit into a feild of 8 digits wide.

 

Try adding a ' format=9.0' to your sql select statement as per my version of your code below to fix the problem:

 

data customer_table;
 length cust_id 8;
 infile cards;
 input cust_id;
cards;
 12345678
 123456789
;
 proc print data=customer_table;
 run;
PROC SQL NOPRINT;
 SELECT 
  CUST_ID format=9.0 INTO : ID_LIST SEPARATED BY ','
 FROM 
  CUSTOMER_TABLE
 ;
 QUIT;
%put &ID_LIST;

   

View solution in original post


All Replies
Esteemed Advisor
Esteemed Advisor
Posts: 7,190

Re: Macro List in sql with numeric value more than 8

Macro language is text, it does not have numbers.  Please see one of the other 10 posts on this subject today.

 

As for your code, you can simply it, and remove any need for macro by simply using SQL sub-querying functionality:

proc sql; 
  create table SALES_INFO as 
  select  *   
  from    SALES_TABLE
  where   SALES_CUST_ID in (select CUST_ID from CUSTOMER_TABLE);
quit;

Do also note that indentation, lower casing, and such like make your code far easier to read.  

Occasional Contributor
Posts: 9

Re: Macro List in sql with numeric value more than 8

Thanks for your reply. 

 

I have already used your suggestion.

 

The issue that I had with this approach is that it is part of an automated process and in case that I would use that, I would have to amend a big part of the code; but the most important is that (at least for my data) it is not very efficient. 

 

When I use the macro list it takes ca 10 mins and when I use the sub-query it takes about an hour...

 

 

Super User
Posts: 787

Re: Macro List in sql with numeric value more than 8

And if you have other uses for &ID_LIST beyond sql, you can add a "format=" name/value pair:

 

SELECT CUST_ID FORMAT=10.0 INTO : ID_LIST ...

Occasional Contributor
Posts: 9

Re: Macro List in sql with numeric value more than 8

Problem solved - Thank you

Solution
‎12-08-2016 08:06 AM
Frequent Contributor
Posts: 130

Re: Macro List in sql with numeric value more than 8

Hi Zatere.

 

The problem is that the default format for numeric variables reformats your 9 digit number to fit into a feild of 8 digits wide.

 

Try adding a ' format=9.0' to your sql select statement as per my version of your code below to fix the problem:

 

data customer_table;
 length cust_id 8;
 infile cards;
 input cust_id;
cards;
 12345678
 123456789
;
 proc print data=customer_table;
 run;
PROC SQL NOPRINT;
 SELECT 
  CUST_ID format=9.0 INTO : ID_LIST SEPARATED BY ','
 FROM 
  CUSTOMER_TABLE
 ;
 QUIT;
%put &ID_LIST;

   

Occasional Contributor
Posts: 9

Re: Macro List in sql with numeric value more than 8

Hi, this is what I was looking for.

Thanks!

☑ This topic is SOLVED.

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

Discussion stats
  • 6 replies
  • 200 views
  • 0 likes
  • 4 in conversation