The issue is formatting how can I get extact format and color. I tried but it seems to be very ugly and not as per what my requirement is!   Please help me with code if you can  🙂 ... View more

data example; input SEGM_DATE datetime18. CLIENT_ID :$9. AVG_WEIGHT; format SEGM_DATE datetime20.; datalines; 01MAR2012:00:00:00 1-ET-1500 8 01APR2012:00:00:00 1-ET-1500 10 01JUN2012:00:00:00 1-ET-1500 13 01JAN2012:00:00:00 2-ET-1500 12 01JUN2012:00:00:00 2-ET-1500 18 ; proc summary data=example nway; class CLIENT_ID; var SEGM_DATE; output out=temp min=min max=max; run; data temp2; set temp; do i=0 to intck('dtmonth',min,max); SEGM_DATE=intnx('dtmonth',min,i);output; end; drop i; keep CLIENT_ID SEGM_DATE; format SEGM_DATE datetime20.; run; proc sql; create table want1 as select a.*,b.AVG_WEIGHT from temp2 as a natural left join example as b order by CLIENT_ID,SEGM_DATE; quit; data want2; set want1; AVG_WEIGHT_month_ago =lag(AVG_WEIGHT); if CLIENT_ID ne lag(CLIENT_ID) then call missing(AVG_WEIGHT_month_ago); AVG_WEIGHT_2month_ago=lag2(AVG_WEIGHT); if CLIENT_ID ne lag2(CLIENT_ID) then call missing(AVG_WEIGHT_2month_ago); run; ... View more

Did you try the solutions I posted? That should solve your problem if you're sticking with this methodology. ... View more

@cosmid wrote: The _n_ is based on the INPUT dataset and not the OUTPUT dataset right? _N_ is based on the number of iterations of the data step. In a simple single set statement data step it behaves like a row or observation counter.   The Set statement may be used multiple times,  inside loops, as the result of conditional statements. Those can have a serious affect on how _n_ relates to numbers of observations. ... View more

DATA HAVE; length R_TYP $250. ; infile datalines ; input R_TYP; datalines; son,daughter uncle aunty son,uncle ; run; DATA TEST_MAPPING; length K_TYP $100. S_TYP $100.; infile datalines delimiter=',' dsd truncover; input K_TYP S_TYP; datalines; son,family daughter,family uncle,extended family aunty,extended family ; run; data want; if _n_=1 then do; if 0 then set TEST_MAPPING; declare hash h(dataset:'TEST_MAPPING'); h.definekey('K_TYP'); h.definedata('S_TYP'); h.definedone(); end; set have; length want temp $ 200; do i=1 to countw(R_TYP,','); temp=scan(R_TYP,i,','); if h.find(key:temp)=0 then want=catx(',',want,S_TYP); else want=catx(',',want,temp); end; keep want; run; ... View more

@AKHarmoning wrote: I am a newbie to SAS on demand for academics. I have been able to create a folder and then a libname and import or create SAS datasets there. My question is whether I can create SAS catalogs in that libname (i.e., types of format or source or macro)? Yes. E.g. the LIBRARY= option of the PROC FORMAT statement allows you to specify where the format catalog is stored. ... View more

Not sure why you want the missing values, but here is a simple data step to check all of the observations for an ID and keep track of whether any of the two specific cases happen or not.  Then just use normal boolean logic to make your new flags. data want; do until(last.id); set have; by id; if flag30 then do; if case='PAA' then PAA=1; else if case='PostDDA' then PostDDA=1; end; _flag30=max(_flag30,flag30); _flag60=max(_flag60,flag60); end; flag30=_flag30=1; flag60=_flag60=1; PAA_only= PAA and not PostDDA; PostDDA_only = PostDDA and not PAA; Multi_form = PostDDA and PAA ; keep id flag30 flag60 PAA_only PostDDA_only Multi_form ; run; Let's try it. data have; length id $9 case $8 ; input case id flag30 flag60; datalines; PAA 001199905 0 0 PAA 001199905 0 0 PAA 001199905 1 1 PostDDA 001199905 1 1 PAA 001587306 1 1 PostDDA 001587306 0 0 PostDDA 001546292 1 1 xxx YYYY 1 1 PAA ZZZZ 0 0 ; Result PostDDA_ Multi_ Obs id flag30 flag60 PAA_only only form 1 001199905 1 1 0 0 1 2 001546292 1 1 0 1 0 3 001587306 1 1 1 0 0 4 YYYY 1 1 0 0 0 5 ZZZZ 0 0 0 0 0 ... View more

That was helpful! 🙂   Thank you.   - Dr. Abhijeet Safai ... View more

@cgates wrote: Thanks! This works except the by 200 element goes past the end date for the last date. In your example I would need it to stop at 31dec2023, but it goes to 18mar2024, since that is 200 days after 01sep2023. The %slices() macro I've posted earlier will stop at the selected end date.  data have; format add_dt date9.; do add_dt='15Feb2020'd to '10Jun2023'd; output; end; run; %slices(work.have,01Jan2020, 20May2023,target_tbl=want); proc sql; select max(ADD_DT) format=date9. from want_year_2023 ; quit;   ... View more

For anyone reading along who is not required to use SQL, or anyone who is required to use a DATA step, or anyone who wants more efficient code than SQL, here is how I would do this in DATA step   data want; set have; ym=input(put(yearmo,6.),yymmn6.); /* Convert YYYYMM to an actual SAS date */ prev_ym=lag(ym); /* YM of previous month */ /* If YM of previous month is one month earlier, then increment value of CONSEC */ if intck('month',prev_ym,ym)=1 then consec+1; /* Otherwise, not consecutive so set consec back to 1 */ else consec=1; format ym yymmn6.; drop yearmo prev_ym; run;    ... View more

Use BasePlus package, it has the %zipArch() macro which does the job, and also the %unzipArch() which un-zips zip created by the first one (of course regular zipps like 7zip or unzip works too).   Example: options dlCreateDir; libname arch1 "%workPath()/testArch1"; libname arch2 "%workPath()/testArch2"; filename arch1 "%workPath()/testArch1"; data _null_; file arch1(test1.txt); put "text for test file 1"; data _null_; file arch1(test2.txt); put "text for test file 2"; data _null_; file arch1(test3.txt); put "text for test file 3"; run; data arch1.class(index=(name)); set sashelp.class; run; data arch1.cars(index=(model)); set sashelp.cars; run; %zipArch( archName1.zip , path = %workPath()/testArch1 , list = 1 , overwrite = 1 ) %zipArch( archName2.zip , pathRef = arch1 , target = %workPath()/testArch2 , list = 1 , overwrite = 1 ) %unzipArch( archName2.zip , path = %workPath()/testArch2 , target = %workPath()/testArch2 , clean=1 , list=1 );   Bart     P.S. How to get BasePlus? To work with SAS Packages like BasePlus or others you will need SAS Packages Framework.   Lets assume your SAS session has access to the internet (if not look below). 1) Create a directory where you want to store SPF and packages, for example let it be:  "/home/myuserid/SASpackages"  under Linux or  "C:\Users\myuserid\SASpackages"  under Windows. 2) Run the following code only one time to get the SPF: filename packages "/home/myuserid/SASpackages"; /* setup directory for packages */ filename SPFinit url "https://raw.githubusercontent.com/yabwon/SAS_PACKAGES/main/SPF/SPFinit.sas"; %include SPFinit; /* enable the framework */ %installPackage(SPFinit) /* install SPF */ %installPackage(BasePlus) /* install BasePlus */ (installation of other packages goes the same way - by installPackage macro). 3) From now on, in this and any new SAS session, to enable SPF and BasePlus just run: filename packages "/home/myuserid/SASpackages"; /* setup directory for packages */ %include packages(SPFinit.sas); /* enable the framework */ %loadPackage(BasePlus) /* load the package content into the SAS session */ 4) Enjoy    P.S.2 No access to the internet on your machine? No problem! Instead step number 2 go here and manually download SPFinit.sas file. Then go to here and download baseplus.zip file. Store them in the directory from step 1 and proceed to step 3.   ... View more

@ajatshatru wrote:  I do not have just a single data_set_2022. I have many monthly tables like this which i need to append in a single data set. So i am using a macro and a loop  I did not write code for the case where you had a single data set. The code I wrote appends all data sets whose name BEGINS WITH data_set_2022. ... View more

I did notice first. variable was not in the output, now I understand why.  Thank you for the information it is very much appreciated.       ... View more

@hamza_saspg wrote: Hi, I need to pass the elements/values of a character variable  into an array :   data have; input var $5. ; datalines; x1 x2 x3 . . .xn run; proc sql; select count(*) into: Nbrvars from dictionary.columns where upcase(libname)="WORK" and upcase(memname)="have" and var not missing; select var into:All_vars separated by " " from dictionary.columns where upcase(libname)="WORK" and upcase(memname)="have" and var not missing; quit; data want; set have; array test[&Nbrvars] &All_vars ; do i=1 to &Nbrvars. ; test[i]="test" /*or any other operation*/ end; run; But the above approach do not give the expected result so I have all the elements of a character variable passed in an array then used to perform other operations.   Thanks in advanced for support. That code wouldn't work - the SQL portion as 'var is not missing' refers to your have dataset which is not in your SQL query.    If that have was your input, what do you expect as output? You say you need to "test whether they are empty or no" Is that per variable or are you looking for rows that are fully empty.    Instead of metadata I would recommend using the _character_ and _numeric_ automatic variables that automatically reference all numeric and character variables - arrays cannot be of mixed types so you'll need two arrays most likely.    But if ultimately what you're after is the number of missing/empty values per column there are easier ways to do this than a data step.    https://communities.sas.com/t5/SAS-Enterprise-Guide/How-would-I-Count-Missing-Values-for-all-Columns-in-a-Table-BY/m-p/376771 And many other options.    The best way to get an answer here is to show your input and the expected output based on that data, to avoid further back and forth due to the xy problem.   The XY problem is a communication problem encountered in help desk, technical support, software engineering, or customer service situations where the question is about an end user's attempted solution (X) rather than the root problem itself (Y or Why?). [1] The XY problem obscures the real issues and may even introduce secondary problems that lead to miscommunication, resource mismanagement, and sub-par solutions. The solution for the support personnel is to ask probing questions as to why the information is needed in order to identify the root problem Y and redirect the end user away from an unproductive path of inquiry. [2] [3]   ... View more