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

Actually, it can be problematic to gracefully break out of a %include - we often face this problem when building SAS-Powered Web Applications, where it's a common requirement to stop processing and show a message to a user WITHOUT a non-zero return code.   The mechanism we use in SASjs development is to "wrap" the %include using this macro:  https://core.sasjs.io/mp__include_8sas.html   And then afterwards, call the following to pick up any aborts:   %mp_abort(mode=INCLUDE) Source for the above:  https://core.sasjs.io/mp__abort_8sas.html ... View more

Add a WHERE statement (or a subsetting IF statement) to eliminate those observations before they can make it to the line that generates to macro call.   Perhaps something like this?: where file_name =: 'BABC_Export' ; Or perhaps where index(file_name,'.') and lowcase(scan(file_name,-1,'.'))='csv' ; ... View more

EXISTS is just for testing whether or not any observations are found in the sub-query (hence the name).   Looks you want to join with the other query instead. proc sql; create table test99 as select b.* , sb.max_create_date from game b left join ( select max(Create_Date) as max_create_date format=date9. , Customer_Name from work.final group by Customer_Name ) sb on b.Customer_Name=sb.Customer_Name ; quit;     ... View more

Yes. you can. Make a BAT file like this : "C:\Program Files\SASHome\SASFoundation\9.4\sas.exe" -nosplash -sysin "D:\RTF\setup.sas" -log "D:\RTF\t_14_3_4_other.log" And execute it under DOS . BTW, “setup.sas" is the code you posted or written by your hand . ... View more

Hello The following blogs will help you understand the concepts of database design  https://en.wikipedia.org/wiki/Database_design  https://en.wikipedia.org/wiki/Database_schema  https://www.datavail.com/blog/an-introduction-to-database-design-from-logical-to-physical/  https://www.astera.com/type/blog/all-you-need-to-know-about-database-design/#:~:text=Database%20design%20is%20defined%20as,for%20the%20proposed%20database%20system.  You can also look at some of  the sample databases that come with Microsoft Access and Microsoft SQL Server . These resources will help how data needs to be put tabular format for different business. requirements. ... View more

This is an example of  PROC TABULATE  which can summarize this data creating one "view" per customer. PROC TABULATE DATA=WORK.A FORMAT=4. ; VAR visit_count; CLASS year_contact branch_no cust_no / ORDER=UNFORMATTED MISSING; TABLE cust_no*visit_count*Sum=" ", branch_no, year_contact ; RUN; ... View more

data a; format x date9.; x=today(); do i=1 to 15; x+1; output; end; run; %macro delete_(indata=,startdate=,enddate=); proc sql; delete from &indata where x between &startdate. and &enddate.; quit; %mend; %delete_(indata=a,startdate='28DEC2020'D,enddate='01JAN2021'd); proc print data=a; run; ... View more

@dennis_oz wrote: Hi all, Please can you advice.   /* When I execute the below code it errors , how can I avoid the below error through code . The below data is read externally and I have no control . However , can you suggest something to overcome the above issue programmatically . */     ERROR: The ID value "'Anti smoking prdcts Freq Buy Eve'n" occurs twice in the input data set.   data foo; infile datalines delimiter=','; input F1 $:100. no1 no2 ; datalines; Anti smoking prdcts Freq Buy Every 2 to 3 months,10,20 Anti smoking prdcts Freq Buy Every 4 to 6 months,30,40 Hair loss prev prod Freq Buy Every 2 to 3 months,40,50 Hair loss prev prod Freq Buy Every 4 to 6 months,60,70 run; proc transpose data=foo out=foo1; id f1; run; SAS currently has a hard limit of 32 characters for a variable name. Your values are identical up to the 32d character as that is all SAS can use to make a variable name from an ID in Proc Transpose. ID values cannot be duplicated within a By group, or without at By group, not duplicated at all. So choices are 1) shorten the text 2) move the time period information to within the first 32 characters so it is included in the shortened version that will be created for variable name, 3) us MUCH shorter text and assign a long Label to the created variable, or 4) one of the report options as mentioned by others. ... View more