About iank131

iank131 · ‎02-04-2017

It would be helpful if you could post some sample data so we write code based on something concrete.

iank131 · ‎02-04-2017

Yes. Or just ignore missing values.

iank131 · ‎02-04-2017

SUM() is also SAS/IML but it works in base SAS. I am wondering why PROD() does not work in base SAS, or if is there an equivalent such function? Thanks.

iank131 · ‎02-04-2017

Using some of the code by collinelliot, if I understand what you require, then try this: data tempfile; INPUT studyID $ environment $ count StartDate :yymmdd10. EndDate :yymmdd10. MvmtDate :mmddyy10. Movementcode Movecode daysbwn Movementcode2 Movecode2; format Startdate EndDate yymmdd10.; datalines; 0001 SCHOOL 1 2007-01-01 2007-01-09 01/09/2007 3 2 8 . . 0001 HOME 2 2007-01-10 2009-01-02 01/02/2009 1 1 721 3 2 0001 SCHOOL 3 2009-01-03 2009-05-04 05/04/2009 3 2 121 1 1 0001 HOME 4 2009-05-05 2015-01-01 05/04/2009 3 2 1917 1 1 0002 SCHOOL 1 2007-01-01 2007-07-25 07/25/2007 1 1 205 . . 0002 HOME 2 2007-07-26 2008-02-22 02/22/2008 3 2 211 1 1 0002 SCHOOL 3 2008-02-23 2010-06-11 06/11/2010 1 1 839 3 2 0002 HOME 4 2010-06-12 2015-01-01 06/12/2010 8 2 1663 8 2 ; run; proc sql; create table tempfile2 as select a.*, b.environment as environment_b, b.StartDate as StartDate_b, b.EndDate as EndDate_b, b.MvmtDate as MvmtDate_b, b.Movementcode as Movementcode_b, b.Movecode as Movecode_b, b.daysbwn as daysbwn_b, b.Movementcode2 as Movementcode2_b, b.Movecode2 as Movecode2_b from tempfile as a, tempfile as b where a.studyID=b.studyID and ((a.count=1 and b.count=2) or (a.count=3 and b.count=4)); quit;

iank131 · ‎02-04-2017

Hi, Here's a real easy problem. It could be due to my PC SAS version 9.4 running Windows 7. I am trying to multiply all the elements of a vector using SAS function PROD, but it is giving me an error "ERROR 68-185: The function PROD is unknown, or cannot be accessed." The vector may include missing values. The idea is very similar to SAS function SUM, which ignores missing values and sums all the values of the vector. For example: data _null_; a = prod(2, 1, ., 3); put "a equals " a; run; But then I get the error. If instead of "prod" I write "sum", there is no error. Is there something wrong with my base SAS? Do other people have the same problem? What am I doing wrong?

iank131 · ‎03-31-2015

Thanks for Reeza's helpful answer. All I need to do is add to my original code following the PROC SQL is the following code that uses the VVALUEX function and I get the data set I want: data want (drop= A B C); set choice_with_index; chosen_index = input( compress(vvaluex(choice)), 2.); run; BTW, I tried putting it inside the PROC SQL, but I get an error since the VVALUEX function only works in data steps. As I didn't know about the VVALUEX function, I found the following post most brief and informative in explaining it: SAS TIP: Dynamic Variable Name — VValuex | TRAJECTORY

iank131 · ‎03-31-2015

I want to dynamically select the column of an input data set in a PROC SQL procedure. I am sure it is an easy thing to do, but I am not sure how to do it. I have following code: data index; input date date9. A B C; format date date9.; datalines; 01JAN2015 1 2 3 02JAN2015 2 3 4 03JAN2015 3 4 5 ;run; data choice; input id date date9. choice $; format date date9.; datalines; 1 02JAN2015 A 2 02JAN2015 B 3 03JAN2015 A 4 03JAN2015 C ; run; proc sql; create table choice_with_index as select choice.*, index.* from choice as a, index as b where a.date = b.date; quit; Instead of selecting all the columns of the index data set (which I do with select index.*), I only want to select the column that corresponds to the value of the variable choice in the choice data set. In other words, I only want the following data set: data want; input id date date9. choice $ chosen_index; format date date9.; datalines; 1 02JAN2015 A 2 2 02JAN2015 B 3 3 03JAN2015 A 3 4 03JAN2015 C 5 ; run; As I said, I suppose this is really easy to do. Could you also tell me what this technique is called? Thanks for your help!

iank131 · ‎03-28-2015

I found an answer to my own question in SAS GLOBAL FORUM 2011, Paper 417-2011, Expanding PROC EXPAND. :smileylaugh: Here is the full code including the data and the PROC EXPAND code. data ds_daily; input id $ date value weight; format date weekdatx17.; datalines; A 2 1 1 A 3 2 1 A 4 3 1 A 5 4 1 A 6 5 1 A 7 6 1 A 8 . . A 9 . . A 10 2 1 A 11 3 1 A 12 4 1 A 13 5 2 A 14 6 2 A 15 . . A 16 . . B 2 1 1 B 3 1 1 B 4 1 1 B 5 2 1 B 6 2 1 B 7 4 1 B 8 . . B 9 . . B 10 1 1 B 11 1 1 B 12 2 1 B 13 2 2 B 14 4 2 B 15 . . B 16 . . ; run; data value_a; set ds_daily; product = value*weight; run; proc expand data=value_a out=value_b from=day to=week align=end; convert product = total_product / method=aggregate observed=total transformin=(setmiss 0); convert weight = total_wght / method=aggregate observed=total transformin=(setmiss 0); id date; by id; run; data value_c (drop=total_product total_wght); set value_b; w_value=total_product/total_wght; run;

iank131 · ‎03-28-2015

Thanks Xia for your answer. It doesn't quite give me what I was looking for, but I get the idea. I had to add a few things to make it get the correct answer. See below. In the meantime I found out how to do it using PROC EXPAND. I add this in reply to my own question. I changed the data slightly to make it give the same output as the code below. data ds_daily; input id $ date value weight; format date weekdatx17.; datalines; A 2 1 1 A 3 2 1 A 4 3 1 A 5 4 1 A 6 5 1 A 7 6 1 A 8 . . A 9 . . A 10 2 1 A 11 3 1 A 12 4 1 A 13 5 2 A 14 6 2 A 15 . . A 16 . . B 2 1 1 B 3 1 1 B 4 1 1 B 5 2 1 B 6 2 1 B 7 4 1 B 8 . . B 9 . . B 10 1 1 B 11 1 1 B 12 2 1 B 13 2 2 B 14 4 2 B 15 . . B 16 . . ; run; data xia_a; set ds_daily; by id; if first.id then week=0; if weekday(date)=1 then week+1; run; proc summary data=xia_a; by id week; var value; weight weight; output out=xia_b mean=avg_value; run; data xia_c; set xia_a; if weekday(date) ne 7 then delete; run; data xia_d (keep=id date avg_value); merge xia_c xia_b; by id week; if date=. then delete; run;

iank131 · ‎03-28-2015

I have daily data with missing values. I want to aggregate to weekly observations by taking the straight average of available values (not counting missing) in the week before the end of the week. I also want to get a weighted average of the available values. Here is the sample code that I am trying, but I can't get the result I want. I realize that the weighted average is not in my code, but I can't even get it to do the straight average. I can't understand the results I get. I even try to transform the missing values into zeros, but still I can't understand what I am getting. luesCould you please help. Thanks! Ian. data ds_daily; input id $ date value weight; format date weekdatx17.; datalines; A 3 2 1 A 4 3 1 A 5 4 1 A 6 5 1 A 7 6 1 A 8 . . A 9 . . A 10 2 1 A 11 3 1 A 12 4 1 A 13 5 2 A 14 6 2 A 15 . . A 16 . . B 3 1 1 B 4 1 1 B 5 2 1 B 6 2 1 B 7 4 1 B 8 . . B 9 . . B 10 1 1 B 11 1 1 B 12 2 1 B 13 2 2 B 14 4 2 B 15 . . B 16 . . ; run; proc expand data=ds_daily out=ds_weekly from=day to=week align=end; convert value = w_value / transformin=( setmiss 0); id date; by id; run; /* Want id date w_value A 8 4 A 15 5.57 B 8 2 B 15 2.29 */

iank131 · ‎03-14-2015

Thanks very much. I learned something about PROC SUMMARY. As I said I tried it with proc means, but I didn't really understand the examples that were given using the weight option. You helped me clarify it, and elegantly too Ian.

iank131 · ‎03-14-2015

I am trying to calculate the daily trade-weighted average of stock prices. That is, I want one daily price per stock which is a calculated as the weighted average price based on the traded volume on each day. I have many stocks and many trades each day per stock. I am able to do this using a combination of data steps and proc means, and even with proc SQL (from ideas seen in another post), but is there are more elegant way of doing it? :smileyconfused: I tried PROC EXPAND, but since I have a different number of obs in each BY group, I am not sure it is possible. It would be great if it could :smileycool:. For example, my data looks something like the following, and my BY group would be CUSIP DATE, my analysis variable would be PRICE, and my WEIGHTS are trdsize: Stock (CUSIP) Date Price Weights (trdsize) A 1 10 100 A 1 11 200 A 1 12 300 A 1 11 350 B 1 30 300 B 1 32 400 B 2 34 100 What I want is this: CUSIP Date trade_wght_price A 1 10*100/(100+200+300+350) + 11*200/950 + 12*300/950 + 11*350/950 B 1 30*300/(300+400) + 32*400/700 B 2 34*100/100 I attach a data file with a more comprehensive sample.

iank131 · ‎11-24-2014

thanks a lot!

iank131 · ‎11-24-2014

Thanks very much. I just happened to find almost exactly the same solution after searching some more. Thanks. Ian.

iank131 · ‎11-24-2014

Could someone tell me an equivalent SAS function for the EXCEL function iferror(), which I could use in a PROC SQL variable assignment. For example, in an datastep if I have a SAS variable defined as: AGE = YRDIF( birthdate, today(), 'ACTUAL'); but for some reason in my data the birthdate is missing, then I get an error. This does not stop SAS, but I would like it to put, for example, 22 (the average age) if the birthdate is missing. In a datastep I would write: if missing(birthdate) then AGE = YRDIF( birthdate, today(), 'ACTUAL'); else AGE = 22; However, in a PROC SQL, how would I do this? All I can think of is if there were an equivalent SAS function like the EXCEL iferror() function, then I could do this in one line. In other words, I would like to write the "equivalent" of (using the EXCEL function): AGE = iferror( YRDIF( birthdate, today(), 'ACTUAL'), 22); If the first argument is null or error, then the second argument is returned for AGE. Thanks in advance. Ian.

Online Status	Offline
Date Last Visited	‎09-18-2018 11:55 PM

Re: Export to multiple Excel files

Re: SAS function PROD not working

Re: SAS function PROD not working

Re: How to combine certain select rows (but not all rows) into single ...

SAS function PROD not working

Re: Proc sql: dynamically selecting column based on input data

Proc sql: dynamically selecting column based on input data

Re: PROC EXPAND daily to weekly aggregation with missing values

Re: PROC EXPAND daily to weekly aggregation with missing values

PROC EXPAND daily to weekly aggregation with missing values

Re: SAS function PROD not working

Re: Proc sql: dynamically selecting column based on input data

Re: Proc sql: dynamically selecting column based on input data

Re: PROC EXPAND daily to weekly aggregation with missing values

Re: Weighted average of a var in a BY group with proc expand??

Re: PROC EXPAND daily to weekly aggregation with missing values

Re: How to combine certain select rows (but not all rows) into single ...

Converting a t-statistic to a p-value

Re: Converting a t-statistic to a p-value

Re: Export to multiple Excel files

Re: SAS function PROD not working

Re: SAS function PROD not working

Re: How to combine certain select rows (but not all rows) into single ...

SAS function PROD not working

Re: Proc sql: dynamically selecting column based on input data

Proc sql: dynamically selecting column based on input data

Re: PROC EXPAND daily to weekly aggregation with missing values

Re: PROC EXPAND daily to weekly aggregation with missing values

PROC EXPAND daily to weekly aggregation with missing values

Re: Weighted average of a var in a BY group with proc expand??

Weighted average of a var in a BY group with proc expand??

Re: SAS function equivalent to EXCEL function iferror()

Re: SAS function equivalent to EXCEL function iferror()

SAS function equivalent to EXCEL function iferror()