Hello All, I am trying to create a list where a certain string is deleted from a string list. for example: considering the third row in the table below where year=2005,2007, the value 2005,2007 should be deleted from year_list and with end result being "2006,2008,2009,2010,2011"  The original dataset looks like this: year year_list 2005 2005,2006,2007,2008,2009,2010,2011 2005,2007 2005,2006,2007,2008,2009,2010,2011 2008,2011 2005,2006,2007,2008,2009,2010,2011 proc sql; create table have (year VARCHAR(1000), year_list VARCHAR(1000)); insert into have values('2005','2005,2006,2007,2008,2009,2010,2011'); insert into have values('2005,2007' '2005,2006,2007,2008,2009,2010,2011'); insert into have values('2008,2011' '2005,2006,2007,2008,2009,2010,2011'); run;   The "want" variable is what i require as output - as shown in the following table (with code below):   year year_list want 2005 2005,2006,2007,2008,2009,2010,2011 2006,2007,2008,2009,2010,2011 2005,2007 2005,2006,2007,2008,2009,2010,2011 2006,2008,2009,2010,2012 2008,2011 2005,2006,2007,2008,2009,2010,2011 2005,2006,2007,2009,2010 proc sql; create table want (year VARCHAR(1000), year_list VARCHAR(1000), want_list VARCHAR(1000)); insert into want values('2005','2005,2006,2007,2008,2009,2010,2011','2006,2007,2008,2009,2010,2011'); insert into want values('2005,2007' '2005,2006,2007,2008,2009,2010,2011','2006,2008,2009,2010,2012'); insert into want values('2008,2011' '2005,2006,2007,2008,2009,2010,2011','2005,2006,2007,2009,2010'); run; Any help would be appreciated. Thank you. ... View more

Hello guys, I hope this message finds you well. I am currently running a "call execute" function through SAS enterprise guide. For whatever reason, when i compile and run the package as a whole i face the following ERROR: THe annoying bit is that if i have to perform a debug exercise, i.e. run all codes one step at a time, i face no issues. I reckon this is some option setting.   data _NULL_; MPRINT(ANALYSEDELIVERYDS): length output_mart_name $50. obs_period $3.; MPRINT(ANALYSEDELIVERYDS): set defsrcdt24; 403 The SAS System 19:13 Thursday, April 7, 2016 MPRINT(ANALYSEDELIVERYDS): output_mart_name=compress(substr(cats("DQ_24M",substr("APSM",1,8),compress(martgen,"/\-")),1,24)); MPRINT(ANALYSEDELIVERYDS): call execute('%DEFAULTDEF(ds = WORK.'||compress(output_mart_name)||',output_excluded='||cats(substr(output_mart_name,1,27),"_excl")||', cod_generation ='||compress(defaultgen)||', observation_period = 24, probation_period = 0);'); MPRINT(ANALYSEDELIVERYDS): call symputx(cats('aaa24',_N_),output_mart_name); NOTE: Line generated by the invoked macro "ANALYSEDELIVERYDS". 10290 compress(_N_)||' as select distinct "aaa24'||compress(_N_)||'" as tmp, "24M" as obs_period, cod_generation as cod_gen, 10290 ! count(1) as counts, sum(default) as totaldef from '||compress(output_mart_ run; data tempdefall12_24; set   ERROR 388-185: Expecting an arithmetic operator. ERROR 76-322: Syntax error, statement will be ignored.   I noticed that my proc sql statement execute through "CALL EXECUTE" is truncated - THe truncated section is underlined and made bold in red above. I even referred the complete bit of this code in the code window below (highlighted in red).     the original code loooks like this: data _NULL_; length output_mart_name $50. obs_period $3.; set defsrcdt24; output_mart_name=compress(substr(cats("DQ_24M",substr("&sc.",1,8),compress(martgen,"/\-")),1,24)); call execute('%DEFAULTDEF(ds = WORK.'||compress(output_mart_name)||',output_excluded='||cats(substr(output_mart_name,1,27),"_excl")||', cod_generation ='||compress(defaultgen)||', observation_period = 24, probation_period = 0);'); call symputx(cats('aaa24',_N_),output_mart_name); call execute('proc sql; create table &workspde..tempdef24'||compress(_N_)||' as select distinct "aaa24'||compress(_N_)||'" as tmp, "24M" as obs_period, cod_generation as cod_gen, count(1) as counts, sum(default) as totaldef from '||compress(output_mart_name)||'; quit;'); run; Please do let me know.   thanks. Sebastian ... View more

Hello All, I spent a day on this and I practically give up. I tried loops, joins and now this, but not able to crack this problem. Background: I want to identify variables from a dataset that have different prefixes which grouped by the same suffixes. Should a prefix be missing from a suffix group, we are displayed a comment noting the missing prefix group – see table at bottom of this post.   My approach: I used proc contents to generate the list of variables. After some minor changes, I created this table below. I tried to transpose the data by failed to get what I want. If this was successful. I would use case when statements and create the comments. However, I don’t think this would work as there could be quite a few variable groups i.e. instead of 3 columns after transpose, I could have 6, and then the commenting system wont work.   What I have: How do I get a dataset as below: dlv_variables prefix suffix GRP_NOMATCH GRP_ NOMATCH INP_PIBESC INP_ PIBESC SCO_PIBESC SCO_ PIBESC GRP_SCOREAPS GRP_ SCOREAPS SCO_SCOREAPS SCO_ SCOREAPS GRP_SCOREFAC GRP_ SCOREFAC INP_SCOREFAC INP_ SCOREFAC SCO_SCOREFAC SCO_ SCOREFAC SCO_TEMP SCO_ TEMP INP_TRIAL INP_ TRIAL data chk8reg1; input dlv_variables $ prefix $ suffix $; datalines ; GRP_NOMATCH GRP_ NOMATCH INP_PIBESC INP_ PIBESC SCO_PIBESC SCO_ PIBESC GRP_SCOREAPS GRP_ SCOREAPS SCO_SCOREAPS SCO_ SCOREAPS GRP_SCOREFAC GRP_ SCOREFAC INP_SCOREFAC INP_ SCOREFAC SCO_SCOREFAC SCO_ SCOREFAC SCO_TEMP SCO_ TEMP INP_TRIAL INP_ TRIAL ;       My failed attempt: The proc transpose feature using the following code creates the following table:   proc transpose data =chk8reg1 out=chk8trans (keep= part2 col:); var dlv_variables; by suffix; run;     suffix COL1 COL2 COL3 NOMATCH GRP_NOMATCH     PIBESC INP_PIBESC SCO_PIBESC   SCOREAPS GRP_SCOREAPS SCO_SCOREAPS   SCOREFAC GRP_SCOREFAC INP_SCOREFAC SCO_SCOREFAC TEMP SCO_TEMP     TRIAL INP_TRIAL       The issue with this table is that COL1 should only contain distinct groups eg. Variable Part1=”GRP”, COL2 should only contain Variable PART1=”INP” And so on and so forth. Order/ sequence does not matter as long as the individual columns consist of individual prefix groups.   In essence, the table should look like this - What I want: I want a table like this: suffix COL1 (GRP_) COL2 (INP_) COL3(SCO_) comment NOMATCH GRP_NOMATCH     INP_NOMATCH and SCO_NOMATCH missing PIBESC   INP_PIBESC SCO_PIBESC GRP_PIBESC missing SCOREAPS GRP_SCOREAPS   SCO_SCOREAPS INP_SCOREAPS missing SCOREFAC GRP_SCOREFAC INP_SCOREFAC SCO_SCOREFAC   TEMP     SCO_TEMP GRP_TEMP and INP_TEMP missing TRIAL   INP_TRIAL   GRP_TRIAL and SCO_TRIAL missing If there are any missing variables COL1, COL2, COL3, then the result should display the missing variables as shown..     If you have any other way to approach this problem, please do let me know. Many thanks. Regards, Seb ... View more

Hello, I am trying to create a hash code, which does the following: 1) A table lookup with output showing matches, 2) Provide a count of the output table (duplicate and nonduplicate) and store value into a variable, I have provided the code in proc sql format which works. THis is the output that i want. I inserted the hash code as well, for any corrections/ changes. I am really keen on learning Hash programming - would u recommend any website/ books with examples? All help would be deeply appreciated.   my proc sql code that works: data sat; input client $; datalines; 531 620 531 908 620 143 run; data main; input keys $; datalines; 530 620 532 909 620 142 run; proc sql; create table duplicates as select keys from main where keys in (select distinct client from sat); select count(1) into :dupcnt from duplicates; run; proc sql; create table nonduplicates as select distinct keys from main where keys in (select distinct client from sat); select count(1) into :nondupcnt from nonduplicates; run; %Put Duplicate count: &dupcnt.; %Put nonduplicate count: &nondupcnt.;         My Hash code is as below: (not working) data duplicates nonduplicates (keep=client); if _n_=1 then do; if 0 then set sat; declare hash w (dataset:"work.sat"); rc=w.definekey('client'); rc=w.definedata('client'); rc=w.definedone(); end; set work.main; rc= w.find(key:keys); if rc eq 0 then output duplicates; else output nonduplicates; run; many thanks.   regards, Sebastian ... View more

Hello all, I am faced with an issue currently, and wondering if any sas gurus can provide me some feedback. I am currently trying to populate a single record for each individual (i.e. tom and jerry) for each year based on the outcome i.e. PASS/ FAIL. The "fail" table will display a single record for each individual each year if the individual has failed at least once that year. The "pass" table will display a single record for each individual each year if the individual has not failed even once in that particular year. I present my input dataset below: data input; input name $6. outcome $5. year 4.; datalines; tom pass 2011 tom pass 2011 tom pass 2011 tom pass 2012 tom fail 2012 tom pass 2012 tom pass 2013 tom pass 2013 tom pass 2013 tom pass 2013 jerry pass 2011 jerry fail 2011 jerry fail 2011 jerry fail 2011 jerry pass 2012 jerry pass 2012 jerry pass 2012 jerry fail 2013 jerry fail 2013 jerry fail 2013 ; run;       The outcome tables are noted below: OUTPUT FAIL Table tom fail 2012 jerry fail 2011 jerry fail 2013   output: pass table tom pass 2011 tom pass 2013 jerry pass 2012   I tried using a datastep procedure i.e. first and last but could not find a way of proceeding further. All help would be greatly appreciated. Many thanks. regards, S.   ... View more

hey guys, this is my code below: DEFAULT is a numeric variable. Risk_grade is a character variable. This code involves transposing from rows to columns. The do loop 'j' is used to look through the variable list in the dataset i.e. RISK_GRADE DEFAULT. Variable "period" is more a counter i.e. _N_ If variables RISK_GRADE and DEFAULT were both numeric this code works perfectly. However, since RISK_GRADE is character and DEFAULT is numeric there is a problem. This also does not work if variables RISK_GRADE and DEFAULT are both character format. ERROR: Variable RISK_GRADE has been defined as both character and numeric. Below is my code:       data workingmodel3NUMtmp ; array Array_list[2,114] $2. RISK_GRADE_A1-RISK_GRADE_A114 DEFAULT_A1-DEFAULT_A114; array var_list[2] RISK_GRADE DEFAULT; retain RISK_GRADE_A1-RISK_GRADE_A114 DEFAULT_A1-DEFAULT_A114 ; KEEP CLIENT_NO SEGMENT_LOCAL SEGMENT_PILLAR_III RISK_GRADE_A1-RISK_GRADE_A114 DEFAULT_A1-DEFAULT_A114; set work.tmp1; by CLIENT_NO; if first.CLIENT_NO then i = 0; i + 1; do j=1 to 2; Array_list[j,period]=var_list[j]; end; if last.CLIENT_NO then output; run;       All help would be appreciated. ... View more