BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
karlbang
Fluorite | Level 6

I am using a simple sql statement

data pdata;
	input parameter $ estimate;
	datalines;
	mu 0
	sigma 1
	;
run;
proc sql noprint;
	select estimate
	into :pmean
	from pdata
	where parameter='mu';
quit;
%put pmean is &pmean.;

that work under windows, but does not work under unix and does not work in my online sas studio. Any suggestions ?

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

Maxim 2: read the log.

You get a "No rows were selected" in your SQL.

After following Maxim 3 (know your data), and inserting a control variable, I found that you have tabs in your code:

data pdata;
	input parameter $ estimate;
  parmhex = put(parameter,$hex16.);
	datalines;
	mu 0
	sigma 1
	;
run;
proc print data=pdata noobs;
run;

Result:

parameter    estimate        parmhex

 	mu             0       096D752020202020
 	sigma          1       097369676D612020

Make it a point to NEVER start datalines at any other column than 1, and DO NOT use tabs in program code (unless you specifically want them as delimiters, in which case you'll have infile datalines dlm='09'x; which makes it clear). Those invisible little creatures are just waiting to sneak up on you and hit you over the head.

See this code instead:

data pdata;
input parameter $ estimate;
datalines;
mu 0
sigma 1
;
run;

proc sql noprint;
select estimate
into :pmean
from pdata
where parameter='mu';
quit;

%put pmean is &pmean.;

Log:

27         data pdata;
28         input parameter $ estimate;
29         datalines;

NOTE: The data set WORK.PDATA has 2 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds
      
32         ;

33         run;
34         
35         proc sql noprint;
36         select estimate
37         into :pmean
38         from pdata
39         where parameter='mu';
40         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

41         
42         %put pmean is &pmean.;
pmean is        0

 

 

View solution in original post

7 REPLIES 7
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Please define: "does not work".  This code works fine for me.  

 

karlbang
Fluorite | Level 6

Log file in Windows

3908  %put pmean is &pmean.;
pmean is        0

Log file in online SAS studio

84         %put pmean is &pmean.;
 pmean is

So the value is not saved as a macro variable (same thing in unix)

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Let me put it another way, I have no way of telling what you are doing, I cannot see your screen, or your system so all I have got to go on here is what you have posted.  Lets start with the datastep:

 

 

data pdata; 
input parameter $ estimate;
datalines;
mu 0
sigma 1
;
run;

 

 

Whats does the dataset look like in the two different systems, is the data correct and matching across them?  I for one never put spaces before a datalines statement for instance:

data pdata;
  input parameter $ estimate;
datalines;
mu 0
sigma 1
;
run;

This alone may be the cause of your issue - as I said I cannot see your screen so I cannot tell you!

Next up the proc sql:

proc sql noprint;
  select estimate
  into :pmean
  from pdata
  where parameter='mu';
quit;

This should work perfectly well on any system, my backend here is linux and it runs fine.  However if the data did not read in properly first off, then parameter='mu' might not be valid, you may need a strip() function around it for instance.  Again, I cannot see your screen!

 

One final thing, you are reading the result in as a number, yet macro is only a character based system, so when you put a number into there it is doing an implicit conversion.  Avoid these.  This:

pmean is        0

 Is showing you that the 0 was implicitly put() into 8. format, which is hwy you can see the spaces.  I would change the proc sql to be explicit (probably wouldn't make any difference to the output in this case, but always good practice):

proc sql noprint;
  select put(estimate,1.)
  into   :pmean
  from   pdata
  where  strip(parameter)='mu';
quit;

 

Kurt_Bremser
Super User

Maxim 2: read the log.

You get a "No rows were selected" in your SQL.

After following Maxim 3 (know your data), and inserting a control variable, I found that you have tabs in your code:

data pdata;
	input parameter $ estimate;
  parmhex = put(parameter,$hex16.);
	datalines;
	mu 0
	sigma 1
	;
run;
proc print data=pdata noobs;
run;

Result:

parameter    estimate        parmhex

 	mu             0       096D752020202020
 	sigma          1       097369676D612020

Make it a point to NEVER start datalines at any other column than 1, and DO NOT use tabs in program code (unless you specifically want them as delimiters, in which case you'll have infile datalines dlm='09'x; which makes it clear). Those invisible little creatures are just waiting to sneak up on you and hit you over the head.

See this code instead:

data pdata;
input parameter $ estimate;
datalines;
mu 0
sigma 1
;
run;

proc sql noprint;
select estimate
into :pmean
from pdata
where parameter='mu';
quit;

%put pmean is &pmean.;

Log:

27         data pdata;
28         input parameter $ estimate;
29         datalines;

NOTE: The data set WORK.PDATA has 2 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds
      
32         ;

33         run;
34         
35         proc sql noprint;
36         select estimate
37         into :pmean
38         from pdata
39         where parameter='mu';
40         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

41         
42         %put pmean is &pmean.;
pmean is        0

 

 

karlbang
Fluorite | Level 6
Thank you very much. Will stop using tabs. Strange that they work under windows.
Kurt_Bremser
Super User

Note that reading text can be a very OS-specific thing, the (superficially) same system call will work differently on Windows, UNIX or z/OS. One sees tabs as "white space", another as part of the data. Leading blanks in place of tabs:

data pdata;
input parameter $ estimate;
datalines;
  mu 0
  sigma 1
;
run;

proc sql noprint;
select estimate
into :pmean
from pdata
where parameter='mu';
quit;

%put pmean is &pm

are transparently discarded, but I'd still not use them.

 

Kurt_Bremser
Super User

PS if you like to use the tab key while entering code, set your enhanced editor to insert blanks in place of tabs, and replace tabs with blanks when opening files. This also prevents confusion when code is viewed on a system with a different tab width setting (eg my tabs in EG are set to 2, tabs in browsers - Firefox, Chrome - are usually set to 4, and tabs on UNIX - and in IE - are set to 8, causing all kinds of hilarious layout).

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 960 views
  • 1 like
  • 3 in conversation