About devsas

devsas · ‎04-29-2018

Thanks Sir, but its quite different problem as here we need the names of drugs concatenated instead. Also, i dont understand that code either.

devsas · ‎04-28-2018

Thanks in advance. I have the data that contains patienid, drugname, drugstartdate, drugenddate. Same patient can have multiple drugs and even within same drug there can be multiple drugstartdate and drugenddates. data have; input ID DRUG $ START_DT :mmddyy. END_DT :mmddyy.; datalines; 1 A 2/17/10 3/19/10 1 A 7/6/10 8/5/10 1 C 7/9/11 9/7/11 1 E 3/1/10 5/30/10 2 B 4/1/12 5/31/12 2 A 7/1/10 7/31/10 2 C 8/3/10 11/1/10 2 D 11/1/13 1/30/14 2 E 12/5/13 3/5/14 2 A 2/1/11 5/2/11 2 F 12/16/13 1/14/14 ; I want to create treatment episode for each patient, where the concomitant meds (taking different meds at the same time) are identified. Also the gaps have a row created with drug name as blank. So, for Id A, my new dataset should be like this: Id druglist streat endtreat 1 A 02/17/10 03/01/10 1 AE 03/1/10 03/19/10 1 E 03/19/10 05/30/10 1 blank 06/1/10 07/5/10 1 A 07/06/10 08/5/10 1 blank 08/6/10 07/8/11 1 C 07/9/11 09/7/11

devsas · ‎03-13-2018

Thanks. Yes, perhaps parenthesis around the subqueries will be needed. I cannot test it as i dont have access to base sas currently

devsas · ‎03-13-2018

Thanks PGStats. Yes, you are correct sept is not needed. Any idea on second part of my question? My guess is adding group by statement at the end of your query should suffice?

devsas · ‎03-12-2018

Good evening everyone! I have multiple tables named in the format - bankX_YYYYMM Where X is name of bank (1,2.3 etc) and YYYY is year and MM is month. Each table has following variables Account_no, registered(indicates if the account is registered), num_trans (number of transactions made during that month), spend (total spend during the time period) 1. I want to have a result which gives me total number of transactions and total spend for "Bank1" during the 4th quarter of 2009. My code- Proc sql; Create table test as select sum(bank1_200909.num_trans, bank1_200910.num_trans, bank1_200911.num_trans, bank1_200912.num_trans) as total_transactions, sum(bank1_200909.spend, bank1_200910.spend, bank1_200911.spend, bank1_200912.spend) as total spend from bank1_200909, bank1_200910, bank1_200911, bank1_200912; quit; Is this correct? 2. What Proc sql query i can write to display the total number of transactions and total spend at "Bank1" and "Bank2", broken out by registered vs non-registered accounts, during jan 2010? Thanks in advance. Please only PROC SQL solution, no data step or macros.

devsas · ‎11-08-2017

Thanks guys. I believe the problem was simply applying the round function in the comparison. I used the following code now and it seems to give me correct solutions. data All_1; set both_ALL ; if (daterange_BZ = date_range) and round(total_premium_biz, .1) = round(total_premium, .1) then GoodMembers = 'Yes'; if (daterange_BZ = date_range) and round(total_premium_biz, .1) ^= round(total_premium, .1) then SpanMatch = 'Yes'; run;

devsas · ‎11-07-2017

Ok thanks. I will check that.

devsas · ‎11-07-2017

Good Afternoon everyone! So, I just realized that the following code doesn't give me perfect results and Im wondering what's going on. data All_1; set both_ALL ; if (daterange_BZ = date_range) and (total_premium_biz = total_premium) then GoodMembers = 'Yes'; if (daterange_BZ = date_range) and (total_premium_biz ^= total_premium) then SpanMatch = 'Yes'; run; daterange_BZ and date_range are both character variables and total_premium_biz/ total_premium are both numeric variables. When I see the results, for some records, goodmembers should be yes (meaning both set of variables match), but the code gives instead spanmatch as yes. Any thoughts?

devsas · ‎11-01-2017

Thanks Sir so much! Really appreciate it. It worked.

devsas · ‎11-01-2017

Good evening everyone! This is little modification of an earlier problem I posted on this thread. As you see in my 'Have' dataset, the months are not continuous sometimes. So, for example, if there is a missing month in consecutive records (jan2014, feb2014, april 2014), I need to break down the range bit differently, so rather than original 'want' data set, my new want data set will look like- data want; length date_range $35 ; input id totalpremium date_range $ ; cards; 1 20 01/01/2014-02/29/2014 1 10 04/01/2014 -04/30/2014 2 30 01/01/2015-02/28/2015 2 15 04/01/2015 -04/30/2015 ; run; If there is no break per id, then the range will be from 1st day of 1st month to last day of lastmonth of that record. Thanks in advance!

devsas · ‎11-01-2017

Just one another way to do this. Perhaps not the most optimum, but if you are more comfortable with Proc SQL and transpose rather than do group processing. proc sql; create table want_ as select item_code, type, sum(sales) as sum_type from have group by item_code, type; quit; proc transpose data = want_ out = want_1 (drop =_name_); by item_code; var sum_type; id type; run; proc sql; create table want_2 as select item_code, sum(sales) as sum_all from have group by item_code; quit; Proc sql; Create table want_final as select a.item_code, a.A as sum_typeA, a.B as sum_typeB, b.sum_all from want_1 a join want_2 b on a.item_code = b.item_code; quit;

devsas · ‎10-26-2017

Good Afternoon everyone! So, I just finished summarizing a report and got everything tied together well, except displaying the date range. I have multiple records for same id's with different dates- all 1st day of the month-in the format 01/01/2014, 02/01/2014, 08/01/2017 and so on. I also have premiums etc which I need to sum for all records for the same id. What I need is to create another variable displaying the date range/span for the same id. data have; input id premium date mmddyy10. ; format date mmddyy10. ; cards; 1 10 01/01/2014 1 10 02/01/2014 1 10 04/01/2014 2 15 01/01/2015 2 15 02/01/2015 2 15 04/01/2015 ; run; data want; length date_range $35 ; input id totalpremium date_range $ ; cards; 1 30 01/01/2014-04/01/2014 2 45 01/01/2015-04/01/2015 ; run; What I have tried so far- Proc sql; create table want as select id, sum(premium) as total premium, min(date)||max(date) as date_range from have group by id; quit; Thanks in advance!

devsas · ‎10-18-2017

Ok, so I finally got it, though im not sure this is the most efficient way as there is so much typing still involved. data test; set fish2_; array all{*} hg n elv sa z da rf fr; array all_mean{*} hg_mean n_mean elv_mean sa_mean z_mean da_mean rf_mean fr_mean ; array all_median{*} hg_median n_median elv_median sa_median z_median da_median rf_median fr_median ; array diff{*} hg_diff n_diff elv_diff sa_diff z_diff da_diff rf_diff fr_diff ; array diffM{*} hg_diff_me n_diff_me elv_diff_me sa_diff_me z_diff_me da_diff_me rf_diff_me fr_diff_me ; do i=1 to dim(all); diff[i]= all_mean{i} - all{i}; diffM[i]= all_median{i} - all{i}; end; run;

devsas · ‎10-18-2017

I believe in this case there is no clean way to define them in arrays since variable names are so different and if I have to write 8 statements to define each difference, what's the point of arrays here. Sorry its beyond me.

devsas · ‎10-17-2017

I did read it and tried to follow that, and this is what I have come up so far. I appreciate your response, but its not really helpful. Its only when I hit the wall after trying all methods, I come here as I understand everyone's time is precious.

Online Status	Offline
Date Last Visited	‎10-04-2019 10:53 PM

Re: Flagging records based on a criteria

Re: Flagging records based on a criteria

Re: Flagging records based on a criteria

Re: Flagging records based on a criteria

Re: Flagging records based on a criteria

Flagging records based on a criteria

How to pick records for ID's which dont have certain codes

Re: Extracting all records for only two unique variable values

Extracting all records for only two unique variable values

Re: How to filter for only one record per provider number

Re: How to pick records for ID's which dont have certain codes

Re: How to pick records for ID's which dont have certain codes

Re: How to filter for only one record per provider number

Re: How to filter for only one record per provider number

Re: Displaying data range in proc sql summary

How to pick records for ID's which dont have certain codes

Re: How to filter for only one record per provider number

Re: Summary of flags

Calculating driving distances based on addresses

Re: counting distinct values over a range of variables

Re: Treatment episode output

Treatment episode output

Re: summing across 4 datasets using proc sql

Re: summing across 4 datasets using proc sql

summing across 4 datasets using proc sql

Re: What's wrong with my code

Re: What's wrong with my code

What's wrong with my code

Re: Displaying data range in proc sql summary

Re: Displaying data range in proc sql summary

Re: summing across rows conditional on columns

Displaying data range in proc sql summary

Re: Proc means summary statistics, join etc

Re: Proc means summary statistics, join etc

Re: Proc means summary statistics, join etc