About bill0101

bill0101 · ‎02-13-2014

Yeah you're welcome. Here's even another equivalent way that does not require a nested sub-expression. You can choose the most efficient one for your data set. By the way, did my solution of FIFO returns work for you? Please go to the question page and give some feedback about whether it works. Thanks. Bill proc sql; create table want_grouped2 as select distinct A.*, coalesce(SUM(B.FLAG),0) as number from have as A left join have as B on A.ID=B.ID and A.TIME-5<=B.TIME<=A.TIME-1 group by A.time order by A.ID, A.time; quit;

bill0101 · ‎02-12-2014

This one is much easier - just further select the same ID within the where constraint. Try this one: data have; input ID $ Time hhmmss8. Flag; format time TIME8.0; cards; A 8:45:01 0 A 8:45:04 1 A 8:45:06 1 A 8:45:08 0 A 8:45:09 1 B 8:45:11 0 B 8:45:13 1 B 8:45:15 0 ;run; proc sql; create table want_grouped as select A.*, (select COALESCE(SUM(FLAG=1),0) from have as B /*kind of self-join*/ where A.ID=B.ID and A.TIME-5<=B.TIME<=A.TIME-1) as number from have as A order by A.ID, A.time; quit; This should give the desired output. Bill

bill0101 · ‎01-27-2014

Hello, 1.The exist() data step function judges the existence of a dataset without unnecessarily accessing data (especially when the dataset is huge). 2.To enable data step functions or routines in a macro, simply enclose it with %sysfunc(). 3. If you need to store the existence state, use %global statement to declare a global variable and use %let to assign value. The above concepts are demonstrated in the following test code. You can change it for your use. Hope this helps Bill /* data A1 does not exist while A2 exists */ data A2; input X @@; datalines; 1 2 ; run; /* conditional execution */ %macro Conditional(); /* get existence directly */ %if %sysfunc(exist(A1)) %then %do; %put execute code here when A1 exists; %end; %else %do; %put execute code here when A1 does not exist; %end; /* store existence into a macro variable for further use */ %global exist2; %let exist2= %sysfunc(exist(A2)); /* enclose to-be-resolved functions with %sysfunc() */ %if &exist2 %then %do; %put execute code here when A2 exists; %end; %else %do; %put execute code herewhen A2 does not exist; %end; %mend; /* execute */ %Conditional(); /* existence of A2 is stored */ %put exist2=&exist2;

bill0101 · ‎01-25-2014

I recommend proc rank for the following 3 reasons: 1. deals with ties appropriately 2. does not require a preceding proc sort 3. very easy to maintain or incorporate with other filter criteria The essence is that proc rank allows you to gain full control over your filter process without modifying the original dataset. Also consider the following situation: * If your advisor or referee asks you to WINSORIZE your data instead of deleting them, you will have no choice but using proc rank. * If you have filter criteria other than percentiles(and I believe this is the usual case), you can easily output a relatively small rank-only dataset to incorporate with them. * If you have many variables that require filtering, you can rank them all at once. Repeating proc sort and data deletion many times will be extremely time-wasting and always leads to unwanted information loss. As I've mentioned earlier, trimming by Nobs (either by dataset option or macro) serves well for one single purpose. However, it is really not a good choice regarding possible future work. I am also dealing with a ~10M obs CRSP dataset, writing a now 1800-lined code and I do not like leaving any known pain in my own ass. Hope my personal experience helps. Bill

bill0101 · ‎01-24-2014

The most concise way to trim a sorted dataset is probably utilizing set option Nobs= . However, I do not recommend doing this for 2 reasons: (1) In many cases, especially for finance researches, ranking or grouping is still needed in further analyses. (2) For nominal or ordinal ranking variables, ties must be treated carefully. Proc rank provides all the fancy options. For reference, there is my working test code. You can see that it does trim 1000 records to 980. /* generate a dataset with 1000 records */ data a; do ID=1 to 10; do x=1 to 100; output; end; end; run; %let trim=0.01; /* trim ratio */ /* conditional output using set option Nobs= */ data b; set a Nobs=NN; if &trim*NN < _N_ <= (1-&trim)*NN then output; run; *expect 980 records in b;

bill0101 · ‎01-24-2014

Hello, proc rank would simply fit your need. There is my own code template: /*** get percentile ***/ /* groups = 100 for percentile (new percentile variable is 0-99)*/ /* for INSERTING a rank variable to the original table, just let <dataset_in> = <dataset_out> */ proc rank data=<dataset_in> groups=100 out=<dataset_out>; var <var1 var2 ...>; by <grouping_vars>; ranks <percentile1 percentile2 ...>; /*variable names of percentile variables*/ run; In case of frequent ties or varying filter values, consider the following more general code: /* fractional rank */ * Note: * (1) fraction: output fractional rank divided by NObs * nplus1: fractional rank divided by NObs+1 * (2) ties=mean: output average rank value for ties; proc rank data=<in_data> Nplus1 ties=mean out=<out_data>; var <x y ...>; by <Grouping_Vars>; ranks <x_rnk y_rnk ...>; run; After ranking, you can easily delete the unwanted value in later data steps. (e.g. if x_rnk>0.99 or x_rnk<0.01 then delete;) Hope this helps. Bill

bill0101 · ‎01-21-2014

The original purpose of my post is to confirm whether a "query using variable tuple" syntax exists or not. Since SAS proc sql does not have "with" syntax so i cannot do it like this post sql - Copy the Value from the column which has the min value in a given observation - Stack Overflow Concatenation does work for this particular dataset. However, it cannot distinguish between something like AB!!A and A!!BA. i.e. it is not robust to data characteristics. I think CatX is OK as long as a correct spacer character is chosen. Therefore I regard it as perfectly correct. Thank you for all your help anyway Bill

bill0101 · ‎01-21-2014

I have found a workaround simply using "proc sort + data merge" It's so simple as if I was such a blockhead. Damn it ~( ￣ c￣)y▂ξ ==================================================== /* most convenient using proc sort + data merge !!!*/ proc sort data=A; /* sort A */ by ID1 ID2; run; proc sort data=list; /* sort list */ by ID1 ID2; run; data A_merge; /* output when (ID1,ID2) is in list dataset*/ merge A list(in=b); by ID1 ID2; if b=1 then output; run; ================================================

bill0101 · ‎01-21-2014

Hello, My question is how to select data on a variable pair (i.e.(ID1,ID2) or more than 2) in a sub-expression of proc sql? The concept is where (ID1, ID2) in (select (ID1, ID2) from list) but the above syntax just doesn't work. Here's the complete attempted test code and log message: (btw, the environment is SAS 9.3 on win7 x64) /***** test code: select on variable pairs *****/ /* data (ID1, ID2, x) */ data A; input ID1 $ ID2 $ x @@; datalines; A X 1 A X 2 A X 3 A Y 1 A Y 2 A Y 3 B X 1 B X 2 B X 3 B Y 1 B Y 2 B Y 3 ; run; /* wanted record: (ID1, ID2)=(A,X) or (B,Y)*/ data list; input ID1 $ ID2 $ @@; datalines; A X B Y ; run; /* attempted code */ proc sql; create table A_list as select * from A where (ID1, ID2) in (select (ID1, ID2) from list); /* select according to a variable tuple */ quit; /***** end test code *****/ log message: 4305 proc sql; 4306 create table A_list as 4307 select * from A 4308 where (ID1, ID2) in (select (ID1, ID2) from list); - - 79 79 ERROR 79-322: Expecting a (. 4308! where (ID1, ID2) in (select (ID1, ID2) from list); ---- - 79 79 ERROR 79-322: Expecting a ). 4309 quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.00 seconds Thanks in advance! Bill

bill0101 · ‎11-21-2013

Took no effect as expected. AFAIK, MIXED =YES works for mixed data type (i.e.numeric and char) in a COLUMN instead of a row. log: 550 /* export xls */ 551 proc export data = a 552 OUTFILE= "D:\a_xls.XLS" 553 DBMS=EXCEL LABEL REPLACE; 554 SHEET="AAA"; 555 NEWFILE=YES; 556 MIXED =YES; 557 run; WARNING: This MIXED statement is not supported and is ignored in Export Procedure. NOTE: File "D:\a_xls.XLS" is removed for replacement. NOTE: File "D:\a_xls.XLS" will be created if the export process succeeds. WARNING: During insert: Data type value could not be converted for column: txt1_label : Data type value could not be converted for column: txt2_label NOTE: "AAA" range/sheet was successfully created. NOTE: PROCEDURE EXPORT used (Total process time): real time 0.36 seconds cpu time 0.18 seconds

bill0101 · ‎11-21-2013

Hello, When I try to export a SAS data set to dbms=excel .xls format, like this (label of variables are written in the first row), txt1_label num1_label txt2_label num2_label A 1 B 2 C 3 D 4 E 5 F 6 G 7 H 8 the character variables of the first obs (the second row) become missing (blank) txt1_label num1_label txt2_label num2_label 1 2 C 3 D 4 E 5 F 6 G 7 H 8 along with a warning message: WARNING: During insert: Data type value could not be converted for column The problem only occurs in the first obs AND the character variables AND dbms=excel. Everything goes fine if I choose dbms=CSV, but this is somehow inconvenient for my work. Test code: =============================== /* create a simple dataset */ data a; input txt1 $ num1 txt2 $ num2 @@; label txt1 = 'txt1_label'; label num1 = 'num1_label'; label txt2 = 'txt2_label'; label num2 = 'num2_label'; datalines; A 1 B 2 C 3 D 4 E 5 F 6 G 7 H 8 ; run; /* export xls */ proc export data = a OUTFILE= "D:\a_xls.XLS" DBMS=EXCEL LABEL REPLACE; SHEET="AAA"; NEWFILE=YES; run; /* export csv*/ proc export data = a OUTFILE= "D:\a_csv.csv" DBMS=CSV LABEL REPLACE; run; ================================= Log of the excel part: =============================== 958 /* export xls */ 959 proc export data = a 960 OUTFILE= "D:\a_xls.XLS" 961 DBMS=EXCEL LABEL REPLACE; 962 SHEET="AAA"; 963 NEWFILE=YES; 964 run; NOTE: File "D:\a_xls.XLS" is removed for replacement. NOTE: File "D:\a_xls.XLS" will be created if the export process succeeds. WARNING: During insert: Data type value could not be converted for column: txt1_label : Data type value could not be converted for column: txt2_label NOTE: "AAA" range/sheet was successfully created. NOTE: PROCEDURE EXPORT used (Total process time): real time 0.39 seconds cpu time 0.15 seconds =============================== SAS version: 9.3 TS1M2 x64_7pro, OS version: win7 x64 enterprise (6.1.7601) Can anybody solve this? Thanks in advance.

bill0101 · ‎11-04-2013

Disclaimer: Non-professional opinion. Use at your own risk. Editing sasv9.cfg works for mine after manually changing all the paths containing "Program Files" or "jre1.6.0_24" to their 8.3 names. 1.Open your sasv9.cfg file under "<your SASHome directory>\SASFoundation\9.3\nls\<your language>" with Notepad for instance, C:\Program Files\SASHome\SASFoundation\9.3\nls\en\sasv9.cfg Of course, backup before editing. 2.Open command prompt window with admin privilege. 3.For each path containing "Program Files" or "jre1.6.0_24" (or other suspicious non-8.3 directory names you would try), type command dir/x under its parent directory to obtain its 8.3 name. For instance, type dir/x under C:> to obtain the 8.3 name of "C:\Program Files" 4.If the 8.3 name does not exist, manually create it using the command fsutil file setshortname <"complete path name"> <short name> For instance, fsutil file setshortname "C:\Program Files (x86)" PROGRA~2 fsutil file setshortname "C:\PROGRA~2\Java\jre1.6.0_24" JRE160~1 This works for my SAS 9.3 TS1M2 + Win7 x64 Enterprise. After maneuvering around sasv9.cfg I finally have my proc reg working. I hope this helps.

Online Status	Offline
Date Last Visited	‎01-02-2020 09:37 AM

Re: By id function in proc sql

Re: By id function in proc sql

Re: Stop proceeding the rest of the program if certain condition is me...

Re: Trimming the top and bottom 1% of data in PROC SQL

Re: Trimming the top and bottom 1% of data in PROC SQL

Re: Trimming the top and bottom 1% of data in PROC SQL

Re: proc sql select on a variable pair(tuple)

Re: proc sql select on a variable pair(tuple)

proc sql select on a variable pair(tuple)

Re: proc export dbms=excel, character variables of first obs (second r...

Re: Trimming the top and bottom 1% of data in PROC SQL

Re: Not able to Paste the code in the Editor Window

Not able to Paste the code in the Editor Window

Re: proc sql select on a variable pair(tuple)

Re: Transpose Question

Re: proc sql select on a variable pair(tuple)

Re: Trimming the top and bottom 1% of data in PROC SQL

Re: Trimming the top and bottom 1% of data in PROC SQL

Re: By id function in proc sql

Re: By id function in proc sql

Re: Stop proceeding the rest of the program if certain condition is me...

Re: Trimming the top and bottom 1% of data in PROC SQL

Re: Trimming the top and bottom 1% of data in PROC SQL

Re: Trimming the top and bottom 1% of data in PROC SQL

Re: proc sql select on a variable pair(tuple)

Re: proc sql select on a variable pair(tuple)

proc sql select on a variable pair(tuple)

Re: proc export dbms=excel, character variables of first obs (second r...

proc export dbms=excel, character variables of first obs (second row) ...

Re: SAS 9.2x32, win7x64, JRE 6.14 - 10 months w/o working ODS gfx