Hello All,
I am trying to create a list where a certain string is deleted from a string list.
for example: the last row of the following table, "want" column, notes the next observation as 2009, as variable year begins with 2008. In other words, the first variable mentioned in year acts as the starting point and every consequent value missing from year when compared to year_list is noted in want. The end point is the last year mentioned in year_list.
The final result should like this:
year | year_list | want |
2008 | 2005,2006,2007,2008,2009,2010,2011 | 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 | 2009,2010 |
I managed to create an array splitting these comma separated values into different variables. however, I am not able to do the comparison, especailly with the starting point.
Please find below my code:
proc sql; create table have (year VARCHAR(100), year_list VARCHAR(100)); 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('2007,2011','2005,2006,2007,2008,2009,2010,2011'); run; data tmp1; set have; csv=countw(year,","); codgen_csv=countw(year_list,","); run; proc sql ; select distinct cats("mart",max(csv)), max(csv) into :arraylist,:arraycnt from tmp1; select distinct cats("dlv",max(codgen_csv)), max(codgen_csv) into :dlv_array, :dlvcnt from tmp1; run; data want (drop=list); set tmp1; length mart1-&arraylist. $100.; length dlv1-&dlv_array. $100.; array mart(&arraycnt.) $; array dlv(&dlvcnt.) $; do _i=1 to dim(mart); mart[_i]=scan(year,_i,','); end; do _j=1 to dim(dlv); dlv[_j]=scan(year_list,_j,','); end; run;
Please do let me know.
Many thanks.
Are we supposed to be able to get to your displayed "want" from the provided "have"? The values for the Year variable don't seem to match. Your first example want has Year of 2008 but the example data has 2005, similar for the third row, Want Year is 2008,2001 but "have" is 2005, 2011.
And why would 2009 be excluded from your want variable for the first row?
Then you create a list of variables from a data set you don't provide. Is this you you actually build data similar to the example Have?
It may be that you want to clarify how you are using this as the bit of building an awkward list to process in a decidely odd manner could be accomplished without those "list" variables.
Also, it may be a good idea to post code using the "Run" icon. the other seems to have inserted some non-printable characters that make your create "have" data to fail when posted into a SAS editor.
Hello Ballardw,
Apologies for the confusion earlier.
I have reviewed the "WANT" table and reinserted the script using the run button.
THe table that i want should be as follows:
year |
year_list |
want |
2005M |
2005M,2006,2007M,2008,2009,2010,2011M |
2006,2007M,2008,2009,2010,2011M |
2005M,2007M |
2005M,2006,2007M,2008,2009,2010,2011M |
2006,2008,2009,2010,2011M |
2007M,2011M |
2005M,2006,2007M,2008,2009,2010,2011M |
2008,2009,2010 |
The reason for adopting the array approach as in the code is as follows:
1) The year and year_list is in character format and comma separated. the year and year_list values are comma separated and sorted.
2) Variable "want" lists all variables from "year_list2 that are not present in variable "year", however ensuring that the starting point is determined by the first value from variable "year" in "year_list" - as seen in the last row.
proc sql;
create table have (year VARCHAR(100), year_list VARCHAR(100));
insert into have values('2005M','2005M,2006,2007M,2008,2009,2010,2011M');
insert into have values('2005M,2007M','2005M,2006,2007M,2008,2009,2010,2011M');
insert into have values('2007M,2011M','2005M,2006,2007M,2008,2009,2010,2011M');
run;
data tmp1;
set have;
csv=countw(year,",");
codgen_csv=countw(year_list,",");
run;
proc sql ;
select distinct cats("mart",max(csv)), max(csv) into :arraylist,:arraycnt from tmp1;
select distinct cats("dlv",max(codgen_csv)), max(codgen_csv) into :dlv_array, :dlvcnt from tmp1;
run;
data want (drop=list);
set tmp1;
length mart1-&arraylist. $100.;
length dlv1-&dlv_array. $100.;
array mart(&arraycnt.) $;
array dlv(&dlvcnt.) $;
do _i=1 to dim(mart);
mart[_i]=scan(year,_i,',');
end;
do _j=1 to dim(dlv);
dlv[_j]=scan(year_list,_j,',');
end;
run;
Possibly this approach is not the best. If you have a better approach/solution in mind, please do let me know.
Thanking you.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.