Howles Tracker

Re: I'm trying to convert SAS code into SQL.

Howles — Mon, 25 May 2015 21:06:38 GMT

The lengths of the variables (especially character variables) are also an issue. So run PROC CONTENTS for each data set created.

Re: Update existing and insert new record to sas dataset.

Howles — Wed, 08 Apr 2015 01:57:11 GMT

UPDATE, or MODIFY??

Re: invert a sequence column in a dataset

Howles — Wed, 25 Feb 2015 02:22:30 GMT

how about

seq = 5 - seq ;

Re: Need help working with the dates

Howles — Thu, 19 Feb 2015 16:08:30 GMT

05/01/2010 is not a valid date literal, but it is a valid numeric expression.

( (5/1)/2010 ) evaluates to 0.0024875, which interprets as 1 January 1960.

Re: Can PROC SQL AVG function be completed within different subsets in the same select statement?

Howles — Wed, 11 Feb 2015 22:12:25 GMT

CASE is meant for this.

PROC SQL;

CREATE TABLE summary AS

SELECT

AVG( case when (c=0) then b else (.) end ) AS Mean_B_C0,

AVG( case when (c=1) then b else (.) end ) AS Mean_B_C1

FROM data;

QUIT;

Re: difference between merge set and update

Howles — Thu, 05 Feb 2015 20:09:47 GMT

also MODIFY

Re: Numeric and Character Observations under a Variable

Howles — Wed, 04 Feb 2015 22:40:33 GMT

Try something like this.

proc format ;

invalue none_zero 'None' = 0 other = [12.] ;

run ;

data _null_ ;

input val none_zero. ;

put val= ;

cards;

123

None

;

Re: Can we use changing dates in proc format?

Howles — Wed, 04 Feb 2015 22:20:06 GMT

The original difficulty arises because the VALUE statement requires literals (not expressions).

But the specs change only from one launch of the program to the next (not on the fly within the program). So %sysfunc and %eval offer a workaround. Try something like this:

Proc Format;

%let td = %sysfunc( today() ) ;

%let wd = %sysfunc( weekday(&td.)) ;

value week %eval(&td.-&wd.- 7) -< %eval(&td.-&wd. ) = week1

%eval(&td.-&wd.-14) -< %eval(&td.-&wd.- 7) = week2 ;

%symdel td wd ;

run;

Re: Picking matched controls

Howles — Wed, 28 Jan 2015 22:11:39 GMT

Go to Macro Collections - sasCommunity and follow Mayo Clinic link.

Re: Date informat MMDDYY10. incorrect?

Howles — Fri, 23 Jan 2015 18:26:16 GMT

Actually that difference is 41972

1 data _null_ ;

2 days_between = '01dec2014'd - '01jan1900'd ;

3 put days_between= ;

4 run ;

days_between=41972

Re: proc sql union and macro

Howles — Sat, 27 Dec 2014 02:55:29 GMT

Why (not) generate code with a macro?

But first, make sure the target code is right. Why the ALLs in the UNIONs?

Re: is there a Book on SAS macros authored by Ian Whitlock?

Howles — Sat, 27 Dec 2014 02:46:24 GMT

1. The (self-anointed) Macro Maven is Ron Fehd, not Ian Whitlock.

2. I would not dismiss the available user-written books about the macro facility.

Re: How to convert all numeric columns to character columns in one shot using SAS

Howles — Mon, 22 Dec 2014 17:16:13 GMT

See

Convert Numeric Variables to Character - sasCommunity

However, the technique might be defeated by the sheer number of columns.

Re: Sql Insert vs Data Step Efficiency Question

Howles — Wed, 17 Dec 2014 21:32:20 GMT

What I don't know is how the SQL statement is optimized by PROC SQL.

Maybe the SELECT is evaluated and the result set is spooled somewhere. Then the rows are batch-inserted into the target table. That seems to be the original poster's theory.

Maybe the SELECT is evaluated and as each row is generated it triggers a single-row insertion operation. That's my hunch.

The results will of course be the same either way, but the incurrence of overhead could differ.

Re: unable to store datetime value in a macro variable

Howles — Sun, 14 Dec 2014 16:00:14 GMT

I observe SQL defaulting to BEST8. The DATA step language uses BEST12.

Try this:

data demo ;

numvar = constant('pi') ;

put numvar ;

run ;

proc sql noprint ;

select numvar into : macrovar from demo ;

%put &macrovar ;

quit ;

Re: proc rank within ranks

Howles — Sun, 16 Nov 2014 17:04:55 GMT

Let var. A be a BY variable in the second PROC RANK call.

Re: converting numbers to time format?

Howles — Thu, 30 Oct 2014 22:08:12 GMT

time = input(put(po_time,z4.),hhmmss4.) ;

format time time5. ;

Re: weired output with except all command

Howles — Wed, 10 Sep 2014 01:47:11 GMT

Don't know what was expected, but the EXCEPT operator is not commutative.

Re: PROC TABULATE and ODS PDF Woes

Howles — Wed, 03 Sep 2014 18:43:33 GMT

That was quick; tech support's response arrived this morning.

The essence: It's a known issue. To work around, code the TABLE statement option INDENT=0.

Once I knew to include INDENT in my search string, I located four pertinent or at least similar SAS notes.

2001-11-28

4438 - PROC TABULATE creates a blank row in HTML/RTF/PDF output- Problem Note

PROC TABULATE may create a blank row in HTML, PDF, and RTF output. This occurs when a CLASS variable is crossed with a statistic in the row dimension and the CLASS variable heading is blanked out. The listing output ...

http://support.sas.com/kb/4/438.html, 28KB

2010-03-17

38296 - First row of a PROC TABULATE table is repeated at the top of each PDF page- Problem Note

The first row of a PROC TABULATE table is repeated at the top of each PDF page.

http://support.sas.com/kb/38/296.html, 27KB

2006-04-25

16880 - TABULATE output may shift in non-LISTING destinations- Problem Note

If an analysis variable comes before a CLASS variable in a TABLE crossing, and a format that applies to the table cells is crossed with the CLASS variable, the output created in HTML, PDF or RTF may show a shifted row. ...

http://support.sas.com/kb/16/880.html, 29KB

2009-04-29

35204 - PROC TABULATE might create a blank row in the ODS PRINTER (PCL/PDF/PS) output destinations- Problem Note

PROC TABULATE might create a blank row in the ODS PRINTER (PCL/PDF/PS) output destinations when multiple CLASS variables are crossed within the row dimension and their labels are blank.

http://support.sas.com/kb/35/204.html, 28KB

Re: PROC TABULATE and ODS PDF Woes

Howles — Tue, 02 Sep 2014 21:36:54 GMT

Thanks for the help. I just turned in a Tech Support form.

A couple of workarounds occur to me.

(1) Get rid of the ALL keywords and use multilevel labels to build totals.

(2) Use RTF destination then use MS WORD to print to PDF

But my real code is a lot more complicated than the example I presented, so I think either of these workarounds would get messy.

Responses to other comments:

Why place a stat keyword and its format in the page dimension? Why not? It simplifies the row and/or column expressions.

Why not use PROC REPORT? I'm not sure it has all the features I need for my real project.