slchen Tracker

Re: How to exclude some names when use proc sql to create macrolist?

slchen — Thu, 06 Jul 2017 20:05:17 GMT

proc sql noprint;
select name into :varlist separated by ' '
from test where name not in ('apple', 'pear') ;
quit;

Re: Counting character variable in array with substr

slchen — Wed, 05 Jul 2017 18:40:38 GMT

If I understand correctly, try this:

data qcodes;

set Patients_NODUPS;

array qcodes {*} Other_Diagnosis_Code_1-Other_Diagnosis_Code_24;

do i = 1 to dim(qcodes);

if substr(left(qcodes),1,1){i}='Q' then do;

output;

return;

end;

run;

Re: DATETIME variable stored as a string

slchen — Sat, 01 Jul 2017 01:43:39 GMT

Convert datetime macro variable, in addition, when compared:

%IF %sysevalf( "&new_maxts"dt > "&max_ts"dt ) %THEN %DO;

Re: Moving observations in the required position

slchen — Tue, 27 Jun 2017 12:47:24 GMT

You could produce a dummy without %, then sort with dummy, like this:

data temp;
set have1;
dummy=compress(translate(f1,'','%'));
run;

proc sort data=temp out=want(drop=dummy);
by dummy;
run;

Or :

proc sql;

create table want as
select * from have1 order by prxchange('s/%//',-1,f1);
quit;

Re: Number Format to Date Format

slchen — Sat, 24 Jun 2017 10:51:11 GMT

data _null_;
date=201403;
_date=input(put(date,6.),yymmn6.);
format _date mmyys7.;
put _all_;
run;

Re: Extracting keywords and corresponding number

slchen — Sat, 24 Jun 2017 00:38:24 GMT

data have;
string="Heart: Normal heart muscle colon (maximal wall thickness = 15mm). Normal aorta (maximal wall thickness = 1.6mm).
Lung: Normal lung (maximal wall thickness = 1.9mm), however movement is absent from the distal part.
Other: Reactive lymphadenopathy is seen . No complications of disease were noted.";
id1=prxparse('/\d+.?\d+\w+/');
id2=prxparse('/(?<=Normal )((\S+ ){1,3})(?=\()/');
start1=1;
start2=1;
end=length(string);
call prxnext(id2,start2,end,string,position2,length2);
call prxnext(id1,start1,end,string,position1,length1);
do while(position1>0);
Name=substr(string,position2,length2);
Number=substr(string,position1,length1);
output;
call prxnext(id2,start2,end,string,position2,length2);
call prxnext(id1,start1,end,string,position1,length1);
end;
keep name number;
run;
proc print;run;

Re: Extract conditional information from text

slchen — Fri, 23 Jun 2017 15:37:00 GMT

name=prxchange('s/_[a-z]+|_\d+$//i',-1,strip(text));

Re: %do without using %to

slchen — Mon, 19 Jun 2017 13:40:18 GMT

If you do want to use %do loop, you could try this:

option minoperator mindelimiter='|';

%macro test;
%let list=13|14|15|25|26|27;
%do part = 1 %to 30;
%if &part in &list %then
%put ∂
%end;
%mend;

%test;

Re: How do I combine multiple columns with missing data and prioritizing certain columns using Proc

slchen — Thu, 15 Jun 2017 18:08:03 GMT

date=coalesce (of date1--date3);

Re: Counting the number of occurrences for a grouped variable

slchen — Thu, 15 Jun 2017 17:46:43 GMT

Method one:
proc sql;

create table want as
select distinct(ID),sum(A) as A, sum(B) as B from have group by ID;
quit;

Method two:

proc summary data=have;
class id;
var A B;
ways 1;
output out=want sum(A B)=A B;
run;

Method three:

data want;
set have;
by id;
retain sumA sumB;
if first.id then call missing(sumA,sumB);
sumA+A; sumB+B;
if last.id then output;
run;

Re: Mask percenatage in data step

slchen — Fri, 09 Jun 2017 12:06:54 GMT

If % is part of text, %nrstr is needed to mask it.

%let k=%nstr(XYZ.DT%DATE.);
data temp;
dsn="&k";
run;

How to Compare Predictor Variables in Multinomial Logistic Regression

slchen — Fri, 10 Feb 2017 17:22:04 GMT

I am running a multinomial logistic regression with proc logistic. The dependent variable is nomial, 0, 1, 2, 3 four categories, the model has four predictors, see Age, Sex, Education and Race, I could use dependent variable=0 as reference, so I could get 1 vs 0, 2 vs 0 and 3 vs 0, but I also want to know how it looks like such as 1 vs 2, 2 vs 3 and 1 vs 3, it is possible to those results by only runing model once with 0 as reference? Or I have to run model several times with dependent categories as reference? if not for proc logistic, how about proc genmod or other procedures? Thank you.

Re: Substituting 3rd element in the list

slchen — Thu, 15 Dec 2016 14:55:12 GMT

It is handy with regular expression.

data _null_;
  string="SVD*H2459**HC:1090F:8P**0~";
  insert=prxchange('s/\*\*/*0*/',1,string);
  put _all_;
run;

Re: Selecting subset of data relative to today's date

slchen — Wed, 07 Dec 2016 19:08:36 GMT

Your EVENT_REPORTED_DATETIME is datetime format, you could try to use datetime():

where EVENT_REPORTED_DATETIME>= intnx('dtmonth',datetime(),-12,'same');

Re: How to find column name which has a value in it

slchen — Thu, 01 Dec 2016 15:27:07 GMT

Try to induce a dummy variable, update to collapse dataset, then find variables without missing values.

data have;

set have;

dummy=1;

run;

data want(keep=Var_nomissing);

update have(obs=0) have end=last;
by dummy;
array var x:;
if last then do;
do over var;
if not missing(var) then Var_nomissing=vname(var);
output;
end;
end;
run;

Re: How to truncate a character variable based on certain sign

slchen — Tue, 29 Nov 2016 17:09:57 GMT

function compress will work even stage_9='U' or 'N'.

new_stage_9=compress(stage_9, '.','kd');

Re: How to group a column based on the other common columns

slchen — Mon, 28 Nov 2016 13:22:59 GMT

proc sql;
select distinct product,date,customer,min(sales_org) as sales_org, sum(actual) as actual from have group by product;
quit;

Re: Please provide the answer using first.id and last.id concept

slchen — Mon, 14 Nov 2016 14:24:47 GMT

proc sql;
select Name,Class,sum(Marks) as Marks from schoolx group by Name,Class;
quit;

Re: how to code this efficiently?

slchen — Fri, 14 Oct 2016 19:19:36 GMT

data have;
input x &$30.;
_x=cats('10',compress(scan(x,1),,'kd'), (round(compress(scan(x,2),,'kd')/7)+1));
cards;
CYCLE4 DAY1
CYCLE4 DAY8
CYCLE4 DAY15
CYCLE4 DAY22
CYCLE4 DAY29
CYCLE5 DAY1
CYCLE5 DAY15
CYCLE5 DAY29
CYCLE6 DAY1
;
proc print;
run;

Re: How to - Concatenate Whole Macro Values with Character Value

slchen — Fri, 22 Jul 2016 17:49:38 GMT

This is what you want?

%Let Variables=Variable1 Variable2 Variable3 Variable4 Variable5;
Data want;
VariablesP=prxchange('s/(\S+)/P$1/',-1,"&variables");
Run;