For an efficient and true dynamic approach, I prefer Hash plus excel libname engine: The following example is to split class into 5 students per sheet, and end up with 4 sheets. libname test "c:\temp\test.xlsx"; data _null_;   declare hash h(ordered:'a'); h.definekey('n');   h.definedata('name','age','weight'); h.definedone(); do n=1 to 5 until (last);   set sashelp.class end=last;   h.add(); end; h.output(dataset: cats('test.split',_n_)); run; libname test clear; Haikuo ... View more

Thqs Art and ksharp for your reply it worked ... View more

Why not use the INT function and discard the offensive decimal part. data _null_;    input dt anydtdtm25.;    put (dt)(=datetime20.2);    nomill = int(dt);    put (nomill dt)(=datetime.);    cards; 2012-07-11 09:43:20.55 ;;;;    run; 799  data _null_; 800     input dt anydtdtm25.; 801     put (dt)(=datetime20.2); 802     nomill = int(dt); 803     put (nomill dt)(=datetime.); 804     cards; dt=11JUL12:09:43:20.55 nomill=11JUL12:09:43:20 dt=11JUL12:09:43:21 ... View more

Or truncate it firstly. data have; input char_date & $40.; date=input(substrn(char_date,1,findc(char_date,'.')-1), ymddttm.); dt=datepart(date); tm=timepart(date); format  dt date9. date datetime.; format tm time.; cards; 2012-07-11 09:43:20.45 2012-07-11 09:53:20.52 ; run; Ksharp ... View more

Dictionary table COLUMNS will keep variable name in the order they are stored in dataset . As long as you can ensure they have the same number of variables. data l2; input z b2 s3; cards; 1 2 3 ; run; data x; input v x2 z3; cards; 1 2 3 ; run; data _null_ ; merge sashelp.vcolumn(keep=name libname memname where=(libname='WORK' and memname='X'))        sashelp.vcolumn(keep=name libname memname where=(libname='WORK' and memname='L2') rename=(name=_name)) end=last; if _n_ eq 1 then call execute('proc datasets library=work nolist memtype=data; modify x; rename '); call execute(catx('=',name,_name)); if last then call execute('; quit;'); run; Ksharp ... View more

Hi, Your post is confusing. 1. your 'id' variable in your sample dataset is numeric, so if it is missing, it can only be '.' instead of '.' or 'blank'. Unless what you mean is all of your variables staring with 'id' are missing, which can be hinted by your output dataset. However, in your second post, you have stated 'id ne .'. 2. What is your purpose? do you want to catenate variable contents or variable names? and on what condition to do so, id eq . or id ne .? all I received is conflicted info from your first and second posts. Haikuo ... View more

How about: data l; input id $ 1-20; cards; aaaa1234a bbbb1234b ccccx1234c asdf1234b ; run; data want(drop=x); set l; x=substrn(left(id),1,1); if not missing (compress(id,x,'d')) ; run; Ksharp Message was edited by: xia keshan ... View more

This post is 3 years.  Start a new question if needed, with test data and required output.  If the initial post is to go by then: new_var=strip(tranwrd(old_var,"0","")); Should work. ... View more

Another Hash() approach: data l; input name $ age ; cards; q 1 . . e 3 . . r 7 . . w 6 ; run; proc sql; select count(name) into :name from dictionary.columns where libname='WORK' AND MEMNAME='L'; %PUT &NAME; data _null_;   if _n_=1 then do;   set l(obs=1); dcl hash h(multidata:'y'); h.definekey('name', 'age'); h.definedata('name', 'age'); h.definedone(); end; do until (cmiss(of _all_)<&NAME); set l; if cmiss(of _all_)<&NAME then h.add(); end; n+1; h.output(dataset:'test'||strip(put(n,best.))); h.clear(); run; Haikuo ... View more

Or if you want to read in the column titles, one way is like this : libname xl excel "&SASForum.\datasets\test_data.xlsx"; proc print data=xl.'Sheet1$A6:B99'n; run;                                                            date_of_                                                emp_num_    join_in_                                         Obs       det       the_org                                           1       12          23                                           2       34          67                                           3       56          78 　 　 　 　 　 　 　 　 　 　 　 　 ... View more

Here is a link to a useful article on the SAS website on how to calculate age from date of birth. http://support.sas.com/kb/24/808.html I've used this technique to answer your question, first of all I've calculated the total number of complete months between the 2 dates and then used this to get the values you need (years is the integer part of total_months/12, months is the remainder of total_months/12, days is the difference between the end date and the start date advanced by total_months. data l; format sdate enddate date9.; sdate='12jan2010'd; enddate=today(); _totmonths=intck('month',sdate,enddate)-(day(enddate)< day(sdate)); years=floor(_totmonths/12); months=mod(_totmonths,12); days=enddate-intnx('month',sdate,_totmonths,'S'); drop _totmonths; run; ... View more

X statment cannot be executed from in a datastep.  It can be executed conditionally through a macro as shown above. In a datastep, use call system, or call execute with the x command inside. data _null_; if &day=6 then do; call system('/path/to/new.sh'); call execute('x /path/to/old.sh'); end; run; ... View more

If the data set contains only these two variables, I would do it like this: *step 1. Delete the first row; data output1; set input; if first.no then delete; rename no=new_no; run; *step 2. Build new variable; data output2; set ouput1; no=lag(new_no); run; Then deal with the first obs of no and last obs of new_no. HTH! ... View more