2020-01-01' '/*Space indicates a possible datetime*/   Remember that SAS stores character strings as fixed length.  There is no way to tell the difference between '2020-01-01' and '2020-01-01     ' once you have the value in a variable. ... View more

Here is a method using PROC SUMMARY that works for your example.  But I worry it might not do what you want with more complex data.  In particular it might not do the duplicate state issue right if there are more than two observations per ID.  Also if the any of the type codes are zero then using the minimum to test if the code is 1 will not work.  And finally (again) it is not clear what criteria you want to use to pick between conflicting types codes other than 1.  This method takes the MAX of the other codes.   proc summary nway data=have(where=(n(type_cd1,type_cd2,type_cd3,type_cd4,type_cd5,type_cd6)) ; class dummyid; var type: ; output out=summary idgroup(out[2] (state)=) min=min1-min6 max=max1-max6 ; run; data want; set summary; * Eliminate duplicate states ; if state_1=state_2 or state_2=' '; * Copy the state ; state=state_1; * Select the calcualted TYPE code ; * Select 1 if present otherwise use the maximum ; array low min1-min6; array high max1-max6; array type Type_CD1-Type_CD6; do index=1 to 6; if low[index]=1 then type[index]=1; else type[index]=high[index]; end; keep dummyid state type:; run; ... View more

So first get rid of the observations with all missing codes.  That is easier with a data step. data not_missing; set have; if n(of Type_CD:); run; Then eliminate those with conflicting STATE codes.  That is easier in PROC SQL. proc sql; create table no_dups as select * from not_missing group by dummyid having count(distinct state)=1 ; quit; Then it looks like you just want to do an UPDATE to collapse the non-missing codes.  data want; update no_dups(obs=0) no_dups; by dummyid ; run; Note that the last non-missing value will be retained by this method.  So if the order matters you should have an ordering variable to make sure the de-duped data records are processed in the right order.  Perhaps there is DATE or other variable you can use to order?   Implementing the any value of 1 overrides the other values rule will be harder.  You could make a set of observations that just have the DUMMYID and a 1 for the particular TYPE code variable and then use that to update the previous results. data any_one; set no_dups(rename=(TYPE_CD1-TYPE_CD6=x1-x6)); array type_cd [6]; array x[6]; do index=1 to 6; if x[index]=1 then do; type_cd[index]=1; output; type_cd[index]=.; end; end; keep dummyid type_cd: ; run; data want; update want any_one; by dummyid; run;   ... View more

Run PROC CONTENTS on both datasets.  Assuming the two datasets are named NEW and OLD so that you used code like this to add the new data to the old dataset. proc append base=old data=new; run; Then the PROC CONTENTS code would look like: proc contents data=old; run; proc contents data=new; run; What does it show as the TYPE and LENGTH of the variable in each dataset?   What does it show as the FORMAT (if any) that is attached to the variable in each dataset?   The TYPE and LENGTH is what DEFINES how the value is STORED. The FORMAT is just special instructions you have told SAS to use when DISPLAYING the values stored in the variable.   You might also want to see what types of values are in each dataset.  For example you could use PROC FORMAT to see the range of values. Remember to REMOVE any format that might be attached to the variable so you can see that ACTUAL values that are in the dataset. proc freq data=old; tables activity_type ; format activity_type; run; proc freq data=new; tables activity_type ; format activity_type; run; IF the TYPE and LENGTH are the same (or at least the length in OLD is long enough for the longest actual value in NEW) AND the types of values are the same then perhaps you just need to apply the format to the OLD dataset. proc dataset nolist lib=WORK; modify OLD; format activity_code $fmtname.; run; quit; But if it looks like the OLD dataset has the the DECODED values stored instead of the ACTUAL values then you are right you do need to modify the data before appending it.     So make a copy of NEW (don't destroy your source data) and make the correction. Then append that modfied copy to the OLD dataset.   Here is a method.  Copy the data from NEW, but rename the variable that needs fixing.  You can pull the the definition of the target variable from the OLD dataset (but don't pull in any of the actual data).  Then you can use the VVALUE() function to get the formatted value of the renamed variable and put it into the right variable. data copy ; set new (rename=(activity_code = _activity_code)) old(obs=0 keep=activity_code)); activity_code =Vvalue(_activity_code); drop _activity_code; run; proc append base=OLD data=COPY; run;     ... View more

That does not describe WHAT you want to do nor WHY you want to do it.  It might describe HOW you are trying to do something.   This really looks like an XY problem.    If you can explain the actual problem you are trying to solve, the data you have (and perhaps since you seem have some type of metadata about the data you want to use to generate a variable list from then describe the metadata you have) and the output you want to get from that input data.  Once that is clear then we can begin to explore optional methods to implement the actual problem.   This comment in the log looks like it might be an attempt at a description: how to create a program which KEEP= a list of variable from new and old by using macro variable because we have multiple datasets with different number of variables (ex new1 new2 old1 old2) THE GOAL is to compare new and old version and find out in KEEP= the difference   But KEEP= is a dataset option. So that seems to be more about how to implement a particular solution and now a description of the actual problem.  How does the dataset option KEEP= relate to actual problem?   What differences are you trying to detect?   Since you seem to want to select only some of the variables (that is what the KEEP= dataset option is for) what is the criteria for deciding which variables you want to keep?  Is the goal to identify the variables that exist in both datasets?   What is meant by "compare new and old version"?  Are you implying that you want to two datasets that are supposed to have the same information to detect the differences between them?  How is the program supposed to know which two datasets to compare?  Are you going to pass that information into the program?  Is it just going to guess to pick OLD1 and NEW1 because the names both end in 1?  Is it some how related the DS variable you had some of the datasets?  But none of the values of that DS variable in any of the example data you provided matches the names of any of the other sample datasets you provided.    Any way if you want to compare datasets use PROC COMPARE. If you want to extend the functionality of PROC COMPARE to make a nice print out of observations that appear in only one of the two datasets then do that separately.  You could use this 25 year old macro from the old master Tom Hoffman. %macro compds /*---------------------------------------------------------------------- Conpare two data sets. ----------------------------------------------------------------------*/ (old= /* Original data set */ ,new= /* Changed data set */ ,keys= /* List of variables that uniquely identify a record */ ); /*---------------------------------------------------------------------- This code was developed by HOFFMAN CONSULTING as part of a FREEWARE macro tool set. Its use is restricted to current and former clients of HOFFMAN CONSULTING as well as other professional colleagues. Questions and suggestions may be sent to TRHoffman@sprynet.com. ----------------------------------------------------------------------- Usage: one or more usage examples ------------------------------------------------------------------------ Notes: additional information ----------------------------------------------------------------------- History: 15MAR98 TRHoffman Creation ----------------------------------------------------------------------*/ %local macro parmerr lkey; %let macro=compds; /* You can get PARMV macro from github %*---------------------------------------------------------------------- Validate macro parameters -----------------------------------------------------------------------; %parmv(OLD,_req=1) %parmv(NEW,_req=1) %parmv(KEYS,_req=1,_words=1) %if (&parmerr) %then %goto quit; */ %*---------------------------------------------------------------------- Replace call to the old PARSEM() macro -----------------------------------------------------------------------; %let lkey=%scan(&keys,-1); data old new deleted added ; set &old(in=in1) &new ; by &keys; if ^(first.&lkey*last.&lkey) then do; if (in1) then output old; else output new; end; else if (in1) then output deleted; else output added; run; proc print data=added; title3 "Records in &new but not in &old"; run; proc print data=deleted; title3 "Records in &old but not in &new"; run; proc compare data=old compare=new; id &keys; title3 "OLD=&old - NEW=&new"; run; title3; %quit: %mend compds; If you want proc compare to show a list of the variables that are not in both datasets then check out the LISTALL option of the PROC COMPARE statement.  ... View more

@valtina1 wrote: Tom, Very helpful script, very glad I found it. However, it works for me only for character variables. When I have only numerical variables (not mixed with character once) and try to transform them, I have an error: ERROR: Too many variables defined for the dimension(s) specified for the array d. array d {40} drugdose:; do index=1 to 40; drugdoses = d[index]; output; end; What did I miss?   Thank you, Val   Why did you tell the data step compiler there were 40 variables whose names start with DRUGDOSE when there aren't 40 of them? Why did you tell it ANY number at all? The data step compiler will count how many variables are in the list you provided it. array d drugdose:; do index=1 to dim(d); drugdoses = d[index]; output; end; ... View more

I am really have a hard time understanding what application this is actually for. At first is sounds like you want a way to generate a list of variables to keep, so perhaps to generate the KEEP= dataset option. Such as in code like:   data want; merge ds1 (keep= a x y z ) ds2 (keep= a b c ) ; by a ; run; For which you would need a dataset like: data keeplist; input dsname :$32. idvar :$32. varlist $200.; cards; ds1 a x y z ds2 a b c ; Or perhaps better would be to normalize the structure like this: data keeplist; input dsname :$32. idvar :$32. varname :$32.; cards; ds1 a x ds1 a y ds1 a z ds2 a b ds2 a c ; Then it is not hard to generate the code from that data: filename code temp; data _null_; set keeplist end=eof; by dsname ; file code ; if _n_=1 then put 'data want;' / 'merge' ; if first.dsname then put dsname '(keep=' idvar @; put varname @; if last.dsname then put ')'; if eof then put ';'/'by ' idvar ';'/ 'run;' ; run; %include code / source2;   ... View more

It is good to use the LENGTH statement to explicitly define the variables.  But you should not define the numeric variables as character.  Also you do not need to include a decimal point when defining the length.  The length can only be an integer value so no decimal point is needed. length item $20 branch $20 id $6 price amount 8; You still need to tell SAS to actually read the variables from the lines of the CSV file by adding an INPUT statement. You cannot have a display width that has more decimal places than the total width.  Fix the width used on the DOLLAR format specification.  Make sure to take into account room for the decimal point (if requested), the $ character and any thousands block separators (commas). data market; length item $20 branch $20 id $6 price amount 8; infile '/home/u62108616/sasuser.v94/S325/market.csv' dsd truncover; input item branch id price amount; format price DOLLAR12.2 amount COMMA15.0; run; No need to use SQL to print data.  Just use PROC PRINT. proc print data=market; run; Also does the CSV files have a header line or not?  If so you will want to add the FIRSTOBS=2 option to the INFILE statement to tell it to skip the header line.       ... View more

Why are you trying to run R code in SAS? What is a SAV file?  Do you mean you have an SPSS dataset? ... View more

You need to provide much more information about what you want the code to do.   Also why did you go to trouble to create an XLSX file and then upload it to the forum to share 6 values?   You could have just typed (or pasted) them into your question and not only would have been easier for you it would have been clearer. dsname varlist ds1 a b c d ds2 b r ds3 c w t So what is it want to do with this list of dataset names and corresponding variable lists? ... View more

If  (and that is probably a big IF) the DX codes are well formed such that they follow the pattern of your example then you CAN use inequality tests on the values.   It is more efficient to set the flag to 0 (false) or 1 (true) and stop the loop as soon as it becomes true. data want; set have; array d d01-d30; do index=1 to 30 until(flag); flag = ('F01' <= d[index] <= 'F19') or ('F30' <= d[index] <= 'F99'); end; drop index; run; If the values can be things like 'D1x' then you probably cannot use the inequality tests.   If the values of the dx variables are messy then make the test more specific. flag = d[index]=:'F' and input(substr(d[index],2),??2.) in (1:19 30:99); ... View more

Seems simple enough.  What out for testing for negative.  Missing values are considered smaller than any actual value. Also you did not say which group should include the zeros, so I made them their own group.  You did not say whether or not you wanted NOBS to count the missing values or not.  I decided to exclude them. data have; input date :yymmdd. mist ; cards; 1996-06-20 -0.0027 1996-06-21 0.0026 1996-06-24 0.0042 1996-06-25 0.0045 1996-06-26 0.0018 1996-06-27 0.0016 1996-06-28 -0.0002 1996-07-01 0.0027 1996-07-02 0.0009 1996-07-03 -0.0005 1996-07-04 0.0070 ; proc sql ; create table want as select year(date) as year , mean(mist) as mean , std(mist) as sd , sum(mist>0) as positive , sum(not (mist>=0)) as negative , mean(case when (mist>0) then mist end) as mean_postive , mean(case when (mist>0) then . else mist end) as mean_negative , count(mist) as nobs , sum(mist=0) as zero , nmiss(mist) as missing from have group by year ; quit; proc print; run;   ... View more

Not sure what you mean by divide?  Do you want to assign a GROUP variable?  Does it need to be random assignment?   If not then simply use their position in the dataset. data want; set have; group = ceil(_n_/20); run;   ... View more