About PGStats

PGStats · ‎02-22-2012

What do you want as a result (score) when a word appears more than once in the phrase? - PG

PGStats · ‎02-18-2012

Variables STARTDATE and ENDDATE must be SAS dates for the intnx function to work. SAS dates are numeric variables. Your problem must be with the way the date variables are imported into a dataset. You would need to provide details of that step to get useful help. PG

PGStats · ‎02-17-2012

The INTNX function is perfect for your purpose : data have; informat startdate enddate yymmdd8.; format startdate enddate yymmdd10.; input startdate enddate; datalines; 20110120 20110220 ; data want; set have; daysToEnd = intnx("MONTH",startdate,0,"END") - startdate; daysToBeginning = enddate - intnx("MONTH",enddate,0,"BEGINNING"); run; proc print; run; PG

PGStats · ‎02-17-2012

You are perfectly right Hai.kuo, there was a clause missing in my SQL query and the equivalent one from Linlin. As we fix this, we can also fix the problem of a sequence hitting discScore=5 many times, as illustrated below. I think the query would be most useful if it read : data have; input incidentId year discScore EmpNo; datalines; 1 2005 2 123 2 2006 5 123 3 2007 0.5 123 4 2008 1 123 5 2009 1.5 123 6 2001 2.5 456 7 2002 3.5 456 8 2003 5 456 8.1 2004 5 456 9 2009 4 789 10 2010 5 789 10.1 2011 3 789 10.2 2012 5 789 10.3 2013 0.5 789 11 2006 5 1011 ; proc sql; create table want as select unique b.incidentId, b.year, b.empNo from have as a inner join have as b on a.empNo = b.empNo and a.year > b.year and a.discScore ne b.discScore where b.discScore >= 5; proc print; run; Then every case where a series of scores hits 5 followed later by some other score would be pulled out. Given the problem statement, it is not clear if the extra condition should read a.discScore ne b.discScore (other score, as stated) or a.discScore < b.discScore (lower score, as was possibly meant). Of course, if the maximum score is 5, then both conditions are equivalent. PG

PGStats · ‎02-17-2012

A self join should do: data have; input incidentId year discScore EmpNo; datalines; 1 2005 2 123 2 2006 5 123 3 2007 0.5 123 4 2008 1 123 5 2009 1.5 123 6 2001 2.5 456 7 2002 3.5 456 8 2003 5 456 9 2009 4 789 10 2010 5 789 11 2006 5 1011 ; proc sql; create table want as select unique a.empNo from have as a inner join have as b on a.empNo=b.empNo and a.year>b.year where b.discScore>=5; PG

PGStats · ‎02-17-2012

That settles that! Thanks. PG

PGStats · ‎02-17-2012

I have a question about something I must have forgotten... Why does test1 fail, but not test2 or test3? data test1; informat d date9.; input d 1-9; datalines; 01JAN1999 ; data test2; length dTxt $9; input dTxt 1-9; d = input (dTxt, date9.); datalines; 01JAN1999 ; data test3; input d :date9.; datalines; 01JAN1999 ; PG

PGStats · ‎02-16-2012

Or simpler yet, a rare opportunity to use a natural full join : proc sql; create table want as select CPFCNPJ, CLIENTE, COD_GRUPO_ATUAL, COD_SEG_ATUAL, sum(RISCO_TOT_ATUAL, RISCO_MOROSO_ATUAL) as RISCO_TOTAL_ATUAL, coalesce(DIAS_ATRASO_1, DIAS_ATRASO_0) as DIAS_ATRASO_ATUAL, RISCO_MOROSO_ATUAL from have1 natural full join have2; PG

PGStats · ‎02-15-2012

Something like such : proc sql; create table want as select coalesce(a.CPFCNPJ, b.CPFCNPJ) as CPFCNPJ, coalesce(a.CLIENTE, b.CLIENTE) as CLIENTE, coalesce(a.COD_GRUPO_ATUAL, b.COD_GRUPO_ATUAL) as COD_GRUPO_ATUAL, coalesce(a.COD_SEG_ATUAL, b.COD_SEG_ATUAL) as COD_SEG_ATUAL, coalesce(RISCO_TOT_ATUAL, 0) + coalesce(RISCO_MOROSO_ATUAL, 0) as RISCO_TOTAL_ATUAL, coalesce(DIAS_ATRASO_1, DIAS_ATRASO_0) as DIAS_ATRASO_ATUAL, RISCO_MOROSO_ATUAL from have1 as a full join have2 as b on a.CPFCNPJ=b.CPFCNPJ; should do the job. PG

PGStats · ‎02-14-2012

Your problem seems to be structured as a hierarchical tree. If you make your dataset look like the output of proc cluster (or a subset thereof) then you could feed it to proc tree for post treatment such as reducing the number of levels or extracting the subtree below a given node. - PG

PGStats · ‎02-14-2012

Learning SQL is costly but a good investment as it can be used with most DBMSs. SAS implementation of SQL is very powerful because it allows almost all of SAS functions and can interact with macro variables. I also find it very efficient. A book on relational databases is probably a better place to start learning SQL than SAS Doc. The details of the first data step are not important and probably not very optimal as I almost never input text data anymore. I had shifted the time window for the summation in order to match the 336 value from your example. I wasn't sure what you wanted exactly. In SAS, date values are in units of days so that refDate+1 is the day after refDate. But it is best to stay independent of the internal date representation. So, ideally, your where condition should read: where d between intnx(interval, refDate, -1, "SAME") and intnx("DAY", refDate, -1, "SAME"). Time intervals such as WEEK2 are understood by the intnx function. I have to go back to the documentation every time I use it. I learned the colon trick the same way you did just a few days ago, on this forum. But it is documented, at least in version 9.3, along with many other variable list notations, it neans all the variables that start with. PG

PGStats · ‎02-13-2012

After which you may want to add : proc transpose data=want out=wantTable(drop=_:); var totalPrecip; by station refDate; id interval; run; to format the table. Good luck.

PGStats · ‎02-13-2012

Try this: data have; format d date11.; input d date9. x1 $ 10-15 x2 $ 16-21 precip 22-30; datalines; 02AUG1994 8 03AUG1994 56 04AUG1994 18 05AUG1994 15 06AUG1994 36 07AUG1994 0 08AUG1994 0 09AUG1994 0 10AUG1994 0 11AUG1994 76 12AUG1994 0 13AUG1994 33 14AUG1994 79 15AUG1994 0 16AUG1994 13 17AUG1994 5 18AUG1994 0 19AUG1994 X2 61 20AUG1994 0 21AUG1994 33 22AUG1994 231 23AUG1994 X1 0 24AUG1994 0 25AUG1994 5 ; proc transpose data=have out=refs(rename=(d=refDate col1=station) where=(notspace(station)>0) drop=_:); by d notsorted; var x:; run; data intervals; input interval $; datalines; WEEK2 WEEK6 MONTH9 ; proc sql; create table want as select station, refDate, interval, sum(precip) as totalPrecip from have, refs, intervals where d between intnx(interval, refDate-1, -1, "SAME") and refDate group by station, refDate, interval;

PGStats · ‎02-13-2012

And that would be something like: proc lifetest data=hepatitis nelson; time dur*status(0); ods output ProductLimitEstimates=ple; run; proc sgplot data=ple; step x=dur y=cumhaz; run;

PGStats · ‎01-19-2012

Very elegant Ksharp. Shows the power of parsing functions, once you master them.

Online Status	Offline
Date Last Visited	‎06-09-2024 06:52 PM

Re: Randomly select 3 rows with condition

Re: Randomly select 3 rows with condition

Re: Requête inner join conditional

Re: How to change order of variables in the graph produced by lines in...

Re: Duplicate value within ID

Re: Notation scientifique dans une variable de type alfanumerique $cha...

Re: Make new variables using if statements in proc sql

Re: How to use GROUP BY to concatenate strings in SAS proc SQL?

Re: Need to remove the exponential from the number

Re: how can get help about "proc split" online ?

Re: Heteroskedasticity

Re: Create a table with increasing integers from 1 to N in SQL

Re: Create a table with increasing integers from 1 to N in SQL

Re: Randomly select 3 rows with condition

Re: How do i parse specific number combinations from a field in a tabl...

Re: Proc Transpose help - "Name of Former Variable" column not recogni...

Re: Using PROC SQL and need to do YEAR(DATE) AS BID_YEAR

Re: Applying Results of Principal Component Analysis on New Data

Re: how can I create a variable that indicates the percentile that eac...

Re: Point = error

Geometric mean for zero values

Convert LAT-LONG to UTM, and back

Finding all single linked chains: the allChains macro

How to find all connected components in a graph

Finding a complete sub matrix (aka finding maximal bicliques)

Word list search

Counting the days from a date to the start/end of the month

Counting the days from a date to the start/end of the month

Looking for exceptions

Looking for exceptions

Input antics - reading date fields with column input.

Input antics - reading date fields with column input.

translate sas data step to proc sql

translate sas data step to proc sql

Looping through table multiple times

finding the sum over intervals of time

finding the sum over intervals of time

finding the sum over intervals of time

Plotting the Nelson-Aalen estimate

character date