differences in proc sql across platforms

[karlbang]

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 ?

[RW9]

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

[karlbang]

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]

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 · Accepted solution]

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]

Thank you very much. Will stop using tabs. Strange that they work under windows.

[Kurt_Bremser]

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]

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).