About slchen

slchen · ‎04-24-2014

You could read all excel files in the same fold to SAS with codes below, then use proc append or data set to append all files. %let filepath=drive:excelfile_fold; filename files pipe "dir/b %bquote(&filepath.\*.xlsx)"; data _null_; infile files truncover; length filename $20.; input; filename=scan(_infile_,1); call execute( 'proc import out='||filename|| 'datafile="%bquote(&filepath.)\'||strip(filename)||'.xlsx" dbms=xlsx replace; run;' ); run;

slchen · ‎04-23-2014

data have; input date yymmdd8.; format date yymmddn8.; cards; 20040102 20040312 20040616 20041231 20060419 20061229 20080223 20081231 ; run; proc sql; select max(date) as date format=yymmddn8. from have group by year(date); quit;

slchen · ‎04-15-2014

It seems that 'M/m' is only alpha character in string, so: substr(string,anyalpha(string),6)

slchen · ‎04-10-2014

proc sql; select name,case when sum(score=1)>=1 then 'yes' else 'no' end as Yes_No from have group by name; quit;

slchen · ‎04-10-2014

Use sashelp.workers as example. %macro test (start,end); %do i=&start %to &end; %do j=3 %to 12 %by 3; data year_&j.&i; set sashelp.workers; if month(date)=&j and year(date)=&i then output; run; %end; %end; %mend test; %test (1977,1980);

slchen · ‎04-07-2014

data test; infile cards truncover; input text $50.; price =(input(prxchange('s/(\w+)_(\d+)_(\w+)/$2/',-1,text),8.))/100; format price dollar8.2; cards; acq_newsale_0_CartChat_0_Flash_1192014 acq_old_3x_GadgetPotomac_7999_Flash_112014 acq_sale_3xconoffer_8999_nacpg_2102014 ; run;

slchen · ‎03-26-2014

Is it what you need? proc sql; select acct, max(field_1) as field_1, max(field_2) as field_2 from yourfile group by acct; quit;

slchen · ‎03-24-2014

data want; set have; do point=1 to nobs; set have (rename=(id=_id age_grp=_age_grp sex=_sex any_c=_any_c)) point=point nobs=nobs; if point>_n_ and id^=_id then do; weigth=sum(age_grp=_age_grp,sex=_sex,any_c=_any_c); if weigth>0 then output; end; end; keep id _id weigth; run; Shenglin

slchen · ‎03-11-2014

%let x =a|b|c|d|e|f ; data _null_; file "c:\temp\temp.csv"; _file_="&x"; put; run; data want; infile "c:\temp\temp.csv" dlm='|'; input x $ @@; run;

slchen · ‎07-26-2013

Some elegant codes were offered, here is other methods with point. data want; do i=0 to 3; do pickit=i+1,i+13,i+9,i+5; set have point=pickit nobs=total; if pickit>total then leave; group=i+1; output; end; end; stop; drop i; run;

slchen · ‎07-13-2013

data test; input var $; cards; BACABBAD ; run; data A_at; set test; do until (index(var,'A')=0); position=index(var,"A"); var=substr(var,position+1); A_at+position; output; end; drop position; run;

slchen · ‎05-07-2013

data have; input id ship_sun ship_mon ship_tue ship_wed ship_thu ship_fri ship_sat ship_date mmddyy10.; format ship_date date9.; cards; 7918 0 0 0 1 0 1 0 05/01/13 1234 1 0 0 0 1 0 0 05/03/13 ; run; data want(drop=day); set have; array weekdays ship_sun--ship_sat; do over weekdays; if weekdays=1 then do; day=scan(vname(weekdays),-1,'_'); if lowcase(put(ship_date,weekdate3.))=day then yes_ship=day; end; end; run; proc print; run;

slchen · ‎05-06-2013

data have; input id visit dose ; cards; 1 1 32 1 2 32 1 3 16 1 4 16 1 5 16 6 1 32 6 2 16 6 3 4 ; run; proc sql; select id,visit,dose, case when flag=1 then 'N' when diff<0 then 'R' else 'P' end as Doseadj from (select *,ifn(dose=max(dose),1,0) as flag, (select a.dose-b.dose from have b where a.id=b.id and a.visit-b.visit=1) as diff from have a); quit; Shenglin

slchen · ‎05-03-2013

data have; input Metric $ Open BD1 BD2; cards; MetricA 10 20 30 Metric2 40 50 60 ; run; proc sort data=have; by metric; run; proc transpose data=have out=have1 name=trans_name; by metric; run; data have2; set have1; new_name=catx('_',metric,trans_name); drop metric t_name; run; proc transpose data=have2 out=want(drop=_name_); id new_name; run; proc print; run;

slchen · ‎05-03-2013

data have; input date date9.; format date date9.; date1=date; format date1 yyq10.; date2=catt('Q',qtr(date),year(date)); cards; 01MAY2012 01JAN2012 01JUL2012 ; proc print; run;

Online Status	Offline
Date Last Visited	‎07-12-2017 03:27 PM

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

Re: Counting character variable in array with substr

Re: DATETIME variable stored as a string

Re: Moving observations in the required position

Re: Number Format to Date Format

Re: Extracting keywords and corresponding number

Re: Extract conditional information from text

Re: %do without using %to

Re: How do I combine multiple columns with missing data and prioritizi...

Re: Counting the number of occurrences for a grouped variable

Re: Last 5 characters from string(irrespective of the length)

Re: Replacing " " with a "_" for variable names in a given file

Re: ODS graphics with more than 5000 points have been suppressed

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

Re: Moving observations in the required position

Re: Import and Append Multiple MS Excel Spreadsheets

Re: how do i keep the latest date possible of that year. (rows)

Re: SCAN(SUBSTRING)

Re: How to flag a group that has at least one particular value

Re: Setup running macro variable

Re: Separating a string value into different variables

Re: merge 1 field from 2 rows with same account

Re: How to match tables based on different criteria

Re: Datalines in macro

Re: ARRAY

Re: SAS index function

Re: How can I determine if shipment date was done on shipment date?

Re: can anyone help me to solve this programming problem?

Re: Flatten a Data File with Dynamic Variable Names

Re: New User Question re Displaying date range into quarter