63510

SAS macro to resolve in proc sql into statement

Question:

Can anyone help me with the syntax error here. Y is dataset which contain some value let's say 1,2,3,4 (Actually it contains many records)

/*This is working fine*/ proc sql; select count(*) into:m from y; select x into:a1 - :a4 from y; quit; %put &m &a1 &a2 &a3 &a4; /*When i am trying to create a macro which will have a1 to a4 values, it's giving me error. below is my approach*/ proc sql; select count(*) into:m from y; select x into:a1 - :a||trim(left(&m.)) from y; quit; %put &m &a1 &a2 &a3 &a4;

Please can someone help me with this, explain me the reason for error.

Answer1:

You don't need to tell how many anymore. SQL will create just enough variables.

data y; do x = 1 to 4; output; end; run; proc sql; select count(*) into:m from y; select x into:a1- from y; quit; %put &m &a1 &a2 &a3 &a4;

This is valid at least as of 9.3.

Answer2:

The syntax error is probably caused by not understanding how the macro processor works. It is just a tool for generating text. The generated text needs to be valid SAS code for it to execute. So trying to write something like:

into :a1 - :a||trim(left(&m.))

will not work. The only macro trigger there is the reference to the M macro variable. So that would evaluate to:

into :a1 - :a||trim(left( 4))

but the INTO operator just wants the name of the upperbound for the macro variable name list there. It cannot handle the || concatenation operator or call functions like TRIM() or LEFT().

Fortunately you do not need these since the INTO clause of PROC SQL is smart enough to only generate as many macro variables as you need. If you are using a current version of SAS you can leave the upperbound empty.

into :a1 -

or if you are running an old version you just use an upperbound that is larger than any expected number of observations.

into :a1-:a999999

Also you do not need to run the query twice to find out how many records SQL found. It will set the count into the automatic macro variable SQLOBS. So your query becomes:

proc sql noprint; select x into :a1- from y; %let m=&sqlobs; quit;

Answer3:

While DN is correct in the other answer that you really don't have to bother with this anymore, I thought it useful to show you the error in your attempt - which was not an unreasonable approach.

You can't use trim the way you did. That's going to operate on character expressions, not on program code - which a macro variable is producing (program code). You've got a few options for how to do this.

First, there is the <a href="http://support.sas.com/documentation/cdl/en/mcrolref/61885/HTML/default/z3514trim.htm" rel="nofollow">%trim autocall macro</a>. That's how you'd do what you were trying to do directly.

proc sql; select count(*) into :ccount trimmed from sashelp.class; select name into :a1 - :a%trim(&ccount.) from sashelp.class; quit; %put &ccount &a1 &a2 &a3 &a4;

Second, SQL will actually do this for you, in two possible options.

<ul><li>trimmed keyword</li> <li>separated by</li> </ul>

The trimmed keyword as part of the into clause, or using separated by even when there's only one resulting value, will cause a trimmed result (so, left-aligned with no spaces following or preceding) to be stored in the destination macrovariable. (Why the defaults are different for separated by vs. not is one of the weird things in SAS related to not breaking code that works, even when it's a silly result.)

proc sql; select count(*) into :ccount trimmed from sashelp.class; select name into :a1 - :a&ccount. from sashelp.class; quit; %put &ccount &a1 &a2 &a3 &a4;

Recommend

  • SQL Server - Splitting a string
  • How do I create a cumulative median field in SAS VA 7.4 Designer?
  • Reading data from a SAS data source in .Net
  • Map predictions back to IDs - Python Scikit Learn DecisionTreeClassifier
  • Skip Characters in Oracle TO_DATE function
  • Efficient & Pythonic way of finding all possible sublists of a list in given range and the minim
  • Click on button in another program - FindWindow, C#
  • VSCode change debug shell to bash on windows
  • Does Apple allow the usage of sysctl.h within iOS applications?
  • Do I need to seed any random number generator before using EVP_PKEY_keygen of OpenSSL?
  • uniform generation of points on 3D box
  • Unable to decode certificate at client new X509Certificate2()
  • Moving mysql files across servers
  • Apache 2.4 and php-fpm does not trigger apache http basic auth for php pages
  • How to recover from a Spring Social ExpiredAuthorizationException
  • How to set/get protobuf's extension field in Go?
  • MySQL WHERE-condition in procedure ignored
  • ILMerge & Keep Assembly Name
  • Cassandra Data Model
  • Perl system calls when running as another user using sudo
  • Deserializing XML into class C#
  • Trying to switch camera back to front but getting exception
  • Importing jscolor library in angular 2
  • Release, debug version and Authorization Google?
  • Large data - storage and query
  • Web-crawler for facebook in python
  • Function pointer “assignment from incompatible pointer type” only when using vararg ellipsis
  • WOWZA + RTMP + HTML5 Playback?
  • SVN: Merging two branches together
  • Hibernate gives error error as “Access to DialectResolutionInfo cannot be null when 'hibernate.
  • Is there a mandatory requirement to switch app.yaml?
  • trying to dynamically update Highchart column chart but series undefined
  • Benchmarking RAM performance - UWP and C#
  • Acquiring multiple attributes from .xml file in c#
  • How to CLICK on IE download dialog box i.e.(Open, Save, Save As…)
  • Can Visual Studio XAML designer handle font family names with spaces as a resource?
  • How can I remove ASP.NET Designer.cs files?
  • python draw pie shapes with colour filled
  • How to Embed XSL into XML
  • java string with new operator and a literal