About snoopy369

snoopy369 · ‎11-01-2013

Well, then I don't see anything obviously wrong. You could try turning off implicit passthrough (NOIPASSTHRU) in the PROC SQL statement and see if that helps, or see if FEEDBACK helps any in explaining what it's doing differently. Sometimes queries involving nulls/missings/etc. are handled differently by DBMSs other than SAS. I'd expect an issue with both elements, but perhaps something's happening that I don't fully understand in the translation to SQL Server code. (NOIPASSTHRU would likely slow your query down, FYI, as it would have to bring all the data back to SAS before summarizing it.)

snoopy369 · ‎11-01-2013

What DBMS is raw_eve297_lps in? Is that a SAS dataset? If not it might be translating the code in some odd form. This works: data testdata; set sashelp.class; if sex='M' then sex=' '; run; proc sql; select sum(case when sex <> ' ' then 1 else 0 end) as num_pop ,count(*) as count ,sum(case when sex <> ' ' then 1.00 else 0.00 end) / count(*) as pct_pop from testdata ; quit; I would also change <> to NE or ^=; while <> does work in PROC SQL, in other places in SAS it's the MAX operator and may confuse other SAS programmers. Also working in SAS: proc sql; select sum(case when sex <> ' ' then 1 else 0 end) as num_pop ,count(*) as count ,calculated num_pop/calculated count from testdata ; quit; You might try that and see if it works better. (Calculated is how you refer to new columns earlier defined in that query).

snoopy369 · ‎11-01-2013

47182 - Adding a leading zero to the %s directive in a PICTURE statement in PROC FORMAT does not insert a leading zero as documented Nope, not fixed (I tested in 9.4). Surprised it hasn't been yet, it's been 3 months since it was reported...

snoopy369 · ‎11-01-2013

How are you verifying the carriage return is removed? If you're opening a dataset up in sas (.sas7bdat), it won't display multiline character fields even if they have CRs in them - it will just eat up the space and display as if that character didn't exist. You'd have to look at the hex value of the sting to see if '0D' was in there somewhere. If that's not the issue (and it doesn't sound like it's the entire issue), then I agree it's probably a C#/.NET issue.

snoopy369 · ‎11-01-2013

This works fine in 9.3 TS1M2 (it doesn't zero pad properly, for some reason, though). You need the decimal in the used part (and at least in 9.3 it only gives 2 places, not 4). proc format; picture mydt low-high='%Y-%0m-%0d %0H:%0M:%0s' (datatype=datetime); run; data _test_; now = datetime()+.05156; put now= mydt.4; run;

snoopy369 · ‎11-01-2013

Thanks Sanjay, that's perfect and makes sense (once I figured out that making the number smaller wasn't helping things!)

snoopy369 · ‎10-31-2013

Here you go. The blue space above the Saturn line extends to the top of the axis, even though the bar (even if I had set barwidth=1) does not extend to the top of the axis. I figure there's probably no way to have it not slightly go past the bar with barwidth < 1.0 but I'd like it to at least only go to the top of where the bar would stop normally.

snoopy369 · ‎10-31-2013

Thanks Dan, that works pretty well. The only followup question I have, is whether I have the control to make it not _overshoot_ the space; currently it's a bit above/below the bars. That might be because of the axis options, but I can't figure out a good way to fix that; I noticed that the scatterplot for the names in the middle also used too much space (the top half were all slightly above where they should be, and the bottom half were below, getting more extreme away from the midpoint) until I added USEDISCRETESIZE to the scatterplot. It looks like the axis offset is probably to blame (since EXTEND is extending it to the axis edge), but none of the OFFSETMIN/MAX seem to help (from what I can tell).

snoopy369 · ‎10-31-2013

http://www2.sas.com/proceedings/forum2007/183-2007.pdf David Cassell's seminal paper on the subject is the best place to start.

snoopy369 · ‎10-30-2013

Close, if I understand properly what you want. proc sql; create table want as select *, sum(case when dummy="B" then assets else 0 end)/sum(assets) as ratio from have group by firm, date; quit;

snoopy369 · ‎10-30-2013

Hmm, that's not preferable as I need to run this multiple times and would rather avoid pulling the value (many) multiple times, but that could be worked around. I'll give it a shot, thanks!

snoopy369 · ‎10-30-2013

Here are two ways to do it. The first only picks one minimum; the second is a bit more work but will pick multiple. WHICHN() function tells you which argument after the first has the value of the first argument (only tells you the first one, though). For both solutions, VNAME() tells you the variable name of the variable in the array. data have; input date1 date2 date3 date4 date5; informat date1-date5 MMDDYY8.; format date1-date5 DATE9.; datalines; 1/1/12 . 5/7/13 . . . 6/5/12 . 6/5/12 . ;;;; run; data want; set have; array dates date1-date5; format minDateVal $200.; minDate = whichn(min(of dates ),of dates ); if (1 le minDate lt dim(dates)) then minDateVal = vname(dates[mindate]); end; put minDateVal=; run; data want; set have; array dates date1-date5; format minDateVal $200.; do _t = 1 to dim(dates); if dates[_t] = min(of dates ) then minDateVal = catx(',',minDateVal,vname(dates[_t])); end; run;

snoopy369 · ‎10-30-2013

I'm trying to make a shaded area showing the overall average for a response value overlaid by a bar chart showing individual groups. Something like below. However, because the bars are nonzero width, the band doesn't fully overlap the top and bottom bars (see the right side) - the band only goes to the midpoint of the bars. I tried playing with DISCRETEOFFSET, which seems like the correct option - but it doesn't seem to do anything. Not sure if I need to adjust OFFSETMIN/MAX to get that to work, tried some but just got very weird results. Any thoughts? This is using SAS 9.4. proc means data=sashelp.cars noprint; class make; output out=make_mpg(where=(_TYPE_=1)) mean(mpg_city)=mpg_city mean(MPG_Highway)=mpg_highway; run; data make_mpg_fin; set make_mpg; format mpg_highway mpg_city 8.1; mpg_average=25; run; ods path work.templat (update) sashelp.tmplmst(read); PROC TEMPLATE; DEFINE STATGRAPH MPGbar2; BeginGraph; layout lattice / columns=3 columngutter=0 columnweights=(.45 .1 .45 ) columndatarange=union; /*error _bar plot starts here*/ column2headers; entry 'Avg Highway MPG '; entry ' '; entry 'Avg City MPG'; endcolumn2headers; Layout OVERLAY / YAxisOpts=( label="" display=none) xAxisOpts=(LABEL="" display=none reverse=true ) walldisplay=none pad=(top=5px); bandplot y=make limitupper=mpg_average limitlower=0; barchartparm X=make Y=mpg_highway /orient=horizontal barwidth=.9 ; scatterplot y=make x=eval(mpg_highway+3)/markercharacter=mpg_highway markerattrs=(size=9pt weight=bold); EndLayout; /*end of the first barchart layout*/ Layout OVERLAY /YAxisOpts=(LABEL="" display=none ) xAxisOpts=(LABEL="" display=none ) walldisplay=none; /*show car maker name*/ ScatterPlot x=_type_ y=Make/markercharacter=make markerattrs=(size=10pt weight=bold); EndLayout; /*end of the car maker layout*/ /*second bar chart for mpg-city*/ Layout OVERLAY / YAxisOpts=(LABEL="" display=none displaysecondary=none) border=false walldisplay=none pad=(top=5px) xAxisOpts=(LABEL="" display=none ); bandplot y=make limitupper=mpg_average limitlower=0/ type=step discreteoffset=0.5; barchartparm X=make Y=mpg_city /orient=horizontal barwidth=.9 ; scatterplot y=make x=eval(mpg_city+3)/markercharacter=mpg_city markerattrs=(size=9pt weight=bold); endlayout;/*end of second bar chart for mpg-city layout*/ endlayout; /*end of lattice*/ EndGraph; END; RUN; ods graphics on /reset=index imagename="MPG" imagefmt=png ANTIALIAS=ON scale=on border=off; PROC SGRENDER DATA=make_mpg_fin TEMPLATE=MPGbar2; RUN; ods graphics off;

snoopy369 · ‎10-29-2013

You can control, to some extent, things with DBSASTYPE, like explained in this other post at the end: https://communities.sas.com/thread/35587?start=15&tstart=0 It's finnicky, and requires SAS 9.2 I believe, but it does let you specify character lengths.

snoopy369 · ‎10-29-2013

I would talk to your SAS sales rep for that sort of information. They should have specific information related to the products and uses you have.

Online Status	Offline
Date Last Visited	‎10-10-2025 01:59 PM

Re: What is the state of SASGF 2022?

Re: Installing add-on package

Re: Help with ods output statement

Re: drill down from visual SAS Visual analytics

Applying control-based filters to subset of data

Re: EG 7.1 code lost during update

Re: Is there a way to use FIRST and LAST options within an IF THEN sta...

Re: Conexão SAS com Python para executar uma rotina

Re: How to transpose many columns to one

Re: Using a SAS Dataset instead of an External file in this interestin...

Re: Help with ods output statement

Re: What is the state of SASGF 2022?

Re: Editing the legend in a map

Editing the legend in a map

Make SAS Studio Log Window Honor the Alt Key

Re: Finding a value in all datasets in all variables

Re: Installing add-on package

Re: proc sgpanel x-axis with multiple scales

Re: Help with ods output statement

Re: Viya 3.5 on Ubuntu 18?

Git and SAS®: A Match Made in (SAS®) Studio

Re: This SQL works in SQL Server Management Studio but not in SAS PROC...

Re: This SQL works in SQL Server Management Studio but not in SAS PROC...

Re: DATETIME22.3 to yyyy-mm-dd time

Re: Saving character field from C# app to sas with carriage returns

Re: DATETIME22.3 to yyyy-mm-dd time

Re: GTL Bandplot overlaying barchart

Re: GTL Bandplot overlaying barchart

Re: GTL Bandplot overlaying barchart

Re: drawing many simple random sample with replacement from a data

Re: Help with a proc sql question

Re: GTL Bandplot overlaying barchart

Re: Output array variable name(s) into one column

GTL Bandplot overlaying barchart

Re: How to widen a column length in IMPORT procedure?

Re: Proc sql vs Proc Fedsql - SAS 9.4

SAS Global Forum 2018

SAS Global Forum