I have the following table. What I would like to do is that if there is missing value between min and max day per subject, I would like to insert 0   subject value day 1 5 1 1 5 2 1 1 3 1 2 5 2 5 1 2 5 2 2 4 4 2 3 5     subject value day 1 5 1 1 5 2 1 1 3 1 0 4 1 2 5 2 5 1 2 5 2 2 0 3 2 4 4 2 3 5   To be honest, i have no idea how to start. I was thinking to transpose and then assign it of 0 but it is too tedious ... View more

Current table subjectid start date starttime 1 23-Nov-19 1213 2 24-Nov-19 1914 Need to combine date and time into one   subjectid start date starttime start_date_time 1 23-Nov-19 1213 11/23/19 12:13 2 24-Nov-19 1914 11/24/19 19:14   I tried to concatenate but it is not a good approach ... View more

I have the following table:   ID reason_1 reason_2 reason_3 1 1 1 0 2 1 0 0 3 0 1 0 4 0 1 1 5       6 1 0 0 7 1 1 0 8 0 1 1 9 0 0 1 10 1 1 0   I would like to assign 1=yes, and 0= no and then calculate frequency and count by each reason by category   Something like this:     no_count no_pct yes_count yes_percent reason_1 4 0.444444 5 55.6% reason_2 3 0.333333 6 66.7% reason_3 6 0.666667 3 33.3%   I tried the following: proc freq data=test; tables reason1 reason2 reason3; run; ... View more

I have the following dataset. I would like to extract first and last date per city and doing a count by city as well   Have:   City dates New York 1/5/2019 Boston 4/20/2019 Washington DC 8/20/2019 Washington DC 9/1/2019 Washington DC 10/19/2019 Washington DC 12/13/2019 Hartford 5/5/2019 Pittsburgh 6/4/2019 Philadelphia 4/6/2019 Philadelphia 6/14/2019 Philadelphia 9/8/2019   Want   City Count firstDate LastDate New York 1 1/5/2019 1/5/2019 Boston 1 4/20/2019 4/20/2019 Washington DC 4 8/20/2019 12/13/2019 Hartford 1 5/5/2019 5/5/2019 Pittsburgh 1 6/4/2019 6/4/2019 Philadelphia 3 4/6/2019 9/8/2019   Possibly also percent per city? like NY will have 1/11, Boston 1/11 (9.1%), etc   Here is waht i have done so far proc sql; select distinct city, count(*) as total_count, min(dates) as firstDate, max(dates) as lastDate from table group by city;quit;   However when I run it, all the dates are converted into NUMBERS, not dates. How do I add n/total % ?    THanks!! ... View more

I have two tables   Table 1 name status Yellow no Blue no Orange no Red no Green no   Table 2 name status Yellow yes Orange yes   Want FINAL: name status Yellow yes Blue no Orange yes Red no Green no   I used the following sql command but i don't want to create additional dataset   proc sql; create table final as select a.name, a.status, b.name, b.status from table1 a left join table2 b on a.name = b.name; quit;   This is the output i see:   name status name status Yellow no Yellow yes Blue no   yes Orange no Orange yes Red no   yes Green no   yes     ... View more

Currently I have this table below:   status not_recruiting status1 recruiting ABCD 1   1 EFGH 1 EFGH 1 HIJK 1 HIJK 1 LMNO 1   1 QRST 1 QRST 1   Basically if status and status1 are not blank then I want the final site_status = "Recruiting" if status1 is blank then i want the final status to be site_status = "Not recruiting"  and I want to delete all the duplicates   i can get the if statement to work but i do not want to create more additional datasets. want to delete in one step ... View more

Following table: name status  version ABCD not recruiting 4 ABCD recruiting 4 EFGH not recruiting 4 HIJK recruiting 4 LMNO not recruiting 4 LMNO recruiting 4 QRST not recruiting 4   Want to only export second entry of the name   name status  version ABCD recruiting 4 EFGH not recruiting 4 HIJK recruiting 4 LMNO recruiting 4 QRST not recruiting 4   I tried using row number but i am losing version ... View more

I have the following table   dataset#1: table1 patientid group age male_gender white_race 1234 placebo 65 1 1 4564 placebo 45 1 0 5646 treatment 55 0 1 6544 placebo 67 1 1 2345 treatment 39 0 0 2343 placebo 47 0 0   I would like to create a following table   Treatment Placebo Male 0/2 (0%) 3/4 (75%) White 1/2 (50%) 2/4 (50%)   I can use proc freq but I am unable to get the denominator for treatment and placebo   proc freq a.table1; table group; run; ... View more

I would like to create a dataset that i want to use as a treatment group as denominator for calculated field after    For example:   patientid group 1234 placebo 4564 placebo 5646 treatment 6544 placebo 2345 treatment 2343 placebo   I want to do something like: group count placebo 4 treatment 2   I want count for placebo and treatment as a stored variable so that it is updated automatically when I use Placebo total denominator as 4 and treatment denominator as 2 for all proc freq calculations   I tried proc sql; create table group_denominator as select group, count(*) as count from test; quit;   but it doesn't do what i would like it to   Thanks ... View more

I want to just pick out the middle instance of "have_id"   have_id want_id 1234-ABCD-5678 ABCD 6564-DGFD-4958 DGFD   Currently I used  proc sql; select have_id, right(left(subjectID, 9), 4) as want_id from testdata; quit;   it works in my sql server but not in the SAS. any help would be great! ... View more

Hello   I have the following dataset   subject_id date volume flag_1 flag_2 1234 1/5/2014 23 0 0 1234 1/6/2014 11 1 0 1234 1/7/2014 93 0 1 1234 1/8/2014 12 0 0 1234 1/9/2014 43 0 0 4432 5/23/2015 32 0 0 4432 5/24/2015 21 0 0 4432 5/25/2015 76 1 0 4432 5/26/2015 23 0 1     What I would like to do is extract values before flag_2. Something like this:   subject_id date volume flag_1 flag_2 1234 1/5/2014 23 0 0 1234 1/6/2014 11 1 0 1234 1/7/2014 93 0 1 4432 5/23/2015 32 0 0 4432 5/24/2015 21 0 0 4432 5/25/2015 76 1 0 4432 5/26/2015 23 0 1   I am completely stuck using PROC SQL;   SELECT subjectid, date, volume, flag_1, flag_2 FROM a.dataset where flag_2 > flag_1;   ... View more