About Haikuo

Haikuo · ‎01-31-2012

Assume you have access to C:\, and you want to use current date to name the folder: data _null_; mc=catx('-',month(today()),put(today(),monyy7.)); call symput('d',cats('md c:\',mc,'\excel\')); run; x "&d"; Please note: TODAY() helps to extract today's date. It also seems to me that dos command "md" does not support blank, so you will end up with no blank between mmm and yyyy. You can also add: options noxwait; at the beginning of the code to avoid manually get out of dos window. Kindly Regards, Haikuo

Haikuo · ‎01-31-2012

Just like Tom has pointed out, your rules are ambiguous. I will just give an example based on your data as is and there are implied rules such as: 1. y and z are mutually exclusive. or 1. z rules triumph over y rules. data have; infile cards; input symbol $ date :mmddyy10. X Y Z; format date mmddyy10.; cards; ABC 1/2/2012 5 . . ABC 1/3/2012 7 . . ABC 1/4/2012 2 . . ABC 1/5/2012 . 20 . ABC 1/6/2012 . . . ABC 1/9/2012 . . . ABC 1/10/2012 5 . . ABC 1/11/2012 11 . . ABC 1/12/2012 5 2 ABC 1/13/2012 8 . . DEF 1/2/2012 5 . . DEF 1/3/2012 7 15 . DEF 1/4/2012 8 . . DEF 1/5/2012 6 . . DEF 1/6/2012 . . 3 DEF 1/9/2012 9 . . DEF 1/10/2012 5 . . DEF 1/11/2012 . . 4 DEF 1/12/2012 . . . DEF 1/13/2012 9 . . ; data want (drop=_:); retain _ry _rz; set have; _ry=ifn(missing(y),_ry,y); _rz=ifn(missing(z),_rz,z); if not missing(x) then do; if missing(_rz) then do; newx=sum(x,_ry); call missing(_ry); end; else if not missing(_rz) then do; newx=x*_rz; call missing (_rz); end; end; run; proc print;run; Kindly Regards, Haikuo

Haikuo · ‎01-31-2012

aha! Many Many Thanks, Tom!!! I knew something wrong with my way of thinking upon macro.

Haikuo · ‎01-31-2012

Thanks, Art. Your answer makes me realize that I have asked a wrong question. Rather, it is more of macro question. To simply my scenario, here is an example: %let m=test1 test2; %let n=_2012; data have; %scan(&m,1)=1 %scan(&m,2)=2; %scan(&m,1)||&n=999; run; I have trouble to make a concatenated variable out of macro variable values. Thanks in advance, Haikuo

Haikuo · ‎01-31-2012

I apologize if this is a bit off-topic. There are numerous ways from table variable names to macro variable values, such as dictionary.column (SQL), sashelp.vcolumn (datastep), proc content, even array-vname(). However, are there any approaches (preferablely non-macro) to do the reverse conversion? eg. converting macro variable values back to table variable names? Thanks, Haikuo

Haikuo · ‎01-30-2012

Hi, It seems to me you would need a 2X DOW loop: data have; infile cards; input Year Month Day Hour Var $; cards; 2011 1 1 1 A 2011 1 1 1 B 2011 1 1 1 C 2011 1 1 1 D 2011 1 1 1 E 2011 1 1 2 A 2011 1 1 2 B 2011 1 1 2 D 2011 1 1 3 C 2011 1 1 3 D 2011 1 1 3 E 2011 1 1 4 A 2011 1 1 4 B 2011 1 1 4 D 2011 1 1 4 E ; data want (drop=_:); length _t $40; retain _t; do until (last.hour); set have; by year month day hour; if first.hour then call missing (_t); _t=cats(_t,var); end; do until (last.hour); set have; length _t $40; by year month day hour; output; if last.hour then do _n_=1 to lengthn(compress('ABCDE',_t)); var=substr(compress('ABCDE',_t),_n_,1); output; end; end; run; proc print;run; Kindly Regards, Haikuo

Haikuo · ‎01-30-2012

Hi Eva, Since reported items are not retained, you would need a non-report temporary variable to retain the value: The following code is just to show how to retain a variable of interest. proc report data=sashelp.class nowd headline out=tst1; column sex age height weight age=no col_a; define sex / group; define height / analysis mean; define no / analysis n "Count"format=comma12.0; compute col_a; temp1+height.mean*no; col_a=temp1; endcomp; run; Say You want to retain col_a, but you can't do it directly by using retain or summary function, however, if you introduce a new Non-reported variable 'temp1', then you get your workaround. HTH, Haikuo

Haikuo · ‎01-28-2012

Or if you have ETS and don't care about sign, here is another way: proc expand data=have out=want; convert var = newvar / transformout=(reverse MOVRANGE 2 reverse); run; Or you need one more step to have your sign, proc expand data=have out=want1; convert var = _lead / transformout=(lead 1); run; data want (drop=time _lead); set want1; newvar=_lead-var; run; As you have noted, proc expand does have sort of LEAD function. Kindly Regards, Haikuo

Haikuo · ‎01-27-2012

I know this is getting silly (I mean my solution), if you want to use LAG() without sorting, here you go: data want (drop=_:); set have(rename=var=_var) end=last; newvar=_var-lag(_var); var=lag(_var); if _n_>1 then output; if last then do; var=_var; call missing(newvar); output; end; run; Kindly Regards, Haikuo

Haikuo · ‎01-27-2012

One approach could be: data have; infile cards; input var; cards; 1 3 4 8 3 7 ; data want (drop=_:); merge have have(firstobs=2 rename=var=_var); newvar=_var-var; run; proc print;run; Have a Nice Weekend! Haikuo

Haikuo · ‎01-27-2012

Thanks! Tom's code is definitely better than flagging _error_. Tom's code can also be further simplified by removing 'by 1 until': data want; set have1; do i=1 to nobs; set have2 point=i nobs=nobs; output; end; run; Kindly Regards, Haikuo

Haikuo · ‎01-27-2012

There are many ways to solve your problem. I assume you only to get rid of the duplicates, not all of obs with duplicates. Proc sort data=have out=want nodupkey; by student_num crstitle coursenum; run; Regards, Haikuo

Haikuo · ‎01-27-2012

My previous code did not count for missing values, it produced table with 513 obs, however, under my settings (SAS 9.2, winxp sp3), it did not alarm any cartisian warnings. So are you using a learning version of SAS? After a minor tweak to acount for the missing values, it outcomes a table with 341 obs. proc sql; create table final3 as select distinct * from FLoat_Leg_fr_GDR (where=(not missing (prev_cpn_date))) a left join lib3rate b on 1 group by a.prev_cpn_date having abs(a.prev_cpn_date-b.AS_OF_DATE)<= min(abs(a.prev_cpn_date-b.AS_OF_DATE) ) ; quit; The reason it has 341 obs instead of 337 is that there are some records have the same date difference between pre and post obs, so they BOTH get to be kept. I wonder if SQL can actuall chose pre or post obs when there is a tie. The benefit of SQL is that data does not need to be pre-sorted, and SQL in this case, will deliver most accurate results just in term of minimum differences. Regards, Haikuo

Haikuo · ‎01-26-2012

I have not tested it. But you may get the idea: %macro splitData(hatches=, sources=, basename=); %do i=1 %to %sysfunc(countw(&hatches)); %do j=1 %to %sysfunc(countw(&sources)); %let hat=%scan(&hatches,&i); %let src=%scan(&sources,&j); data &basename&hat&src; set have; where hatch=&hat and source="&src"; run; data _null_; if e>0 then call execute('proc export data=&basename&hat&src outfile="&basename&hat&src..dbf" dbms=dbf replace;run;'); set &basename&hat&src nobs=e; run; %end; %end; %mend; %splitData(hatches=&hatches, sources=&sources, basename=test) Regards, Haikuo

Haikuo · ‎01-26-2012

Here is a smaller Macro hopefully to tackle your specific problem. data have; infile cards; input Key Value $; cards; 1 this 2 that 1 here 2 there ; proc sql; select distinct key into:key separated by ' ' from have; quit; %macro dist; %doi=1 %to %sysfunc(countw(&key)); data want&i; set have; where key=%scan(&key,&i); run; %end; %mend; %dist Kindly Regards, Haikuo

Online Status	Offline
Date Last Visited	‎02-27-2023 12:47 PM

Re: How to convert GPS Coordinates with degree into longitudes and lat...

Re: Multiple Observations from Single Record

Re: Filling in missing sequence number and linearly interpolating betw...

Re: repeat observation by id

Re: repeat observation by id

Re: Output from Proc Datasets as a data file

Re: Warning in log - why?

Re: complicated conversion

Re: assign unscheduled visits

Re: Use a multi-selection macro variables from a prompt in pass throug...

Re: PROC JSON - create a hierarchical file

Re: Copy values based on certain criteria

Re: Importing a file with multiple delimiters per record

How to handle line breaks in csv files

Re: Importing a .CSV file from R with NA's as missing

Re: capitalize first letter of first word

Re: Copy dataset structure

Re: how to check if user is running any session and using SAS metadata...

Re: Remove leading and trailing zeros from character field

Re: how to get valid data

Re: Automatic Directory Creation - Windows

Data processing question

Reduce hard coding by using the VCOLUMN table in SASHELP to get a list...

Reduce hard coding by using the VCOLUMN table in SASHELP to get a list...

Reduce hard coding by using the VCOLUMN table in SASHELP to get a list...

Re: How to add missing data

proc report: aggregate a computed column

Re: how to generate variable for observation N based on information fr...

Re: how to generate variable for observation N based on information fr...

how to generate variable for observation N based on information from o...

Re: Merging data sets

finding duplicate records

Re: how to find closest match that is less than its date?

How to split and export a dataset into multiple files

Re: How to split and export a dataset into multiple files

SAS Analytics Explorers