Hi,
I have this dataset and this is the result I desire
Task_Name | Assigned_To | result | count |
Admin QC | tcollins@clinical.com | Admin QC | |
Image Preview | vpandya@clinical.com | Image Preview | |
Admin QC | tcollins@clinical.com | Admin QC | |
Image Preview | tcollins@clinical.com | Image Preview | |
Admin QC | tcollins@clinical.com | Admin QC | |
Image Preview | vpandya@clinical.com | Image Preview | |
Photo Image Preview QC | tcollins@clinical.com | Photo Image Preview QC | |
Admin QC | araju@clinical.com | Admin QC | |
Image Preview | arodriguez@clinical.com | Image Preview | |
Admin QC | tcollins@clinical.com | Admin QC | |
Image Preview | tcollins@clinical.com | Image Preview | |
Admin QC | vpandya@clinical.com | Admin QC | |
Image Preview | tcollins@clinical.com | Image Preview | |
Admin QC | araju@clinical.com | Admin QC | |
Image Preview | arodriguez@clinical.com | Image Preview | |
Admin QC | marnofsky@wcclinical.com | Admin QC | |
Image Preview | tcollins@clinical.com | Image Preview | |
Admin QC | vpandya@clinical.com | Admin QC | |
Image Preview | tcollins@clinical.com | Image Preview | |
Admin QC | tcollins@clinical.com | Admin QC | |
Image Preview | tcollins@clinical.com | Image Preview | |
Admin QC | vpandya@clinical.com | Admin QC | |
Image Preview | tcollins@clinical.com | Image Preview | |
Admin QC | araju@clinical.com | Admin QC | |
Image Preview | arodriguez@clinical.com | Image Preview | |
Admin QC | 1` | ||
Admin QC | marnofsky@clinical.com | Admin QC | |
Image Preview | tcollins@clinical.com | Image Preview | |
irRC RECIST Oncology | rbht@bidmc.harvard.edu | ||
Post Pre-Derm QC | cwalsh@clinical.com | Post Pre-Derm QC | |
Post Review QC 1 | araju@clinical.com | Post Review QC 1 | |
Post Review QC 2 | araju@clinical.com | Post Review QC 2 | |
Pre-Dermatology | cwalsh@clinical.com | Pre-Dermatology | |
Radiology Review 1 | csado@partners.org | ||
Radiology Review 2 | vassah@hotmail.com | ||
irRC RECIST Oncology |
and I want an idea to get the result I want
If the scan if scan(assigned_to,2,"@")="clinical.com" then result=assigned_to;
I want to create a variable that has the values that associated with the clinical.com email from the variable assigned_to
but also I want to create a variable called count witch count the blanks in the assigned_to variable if the value is in the result variable
Create your list then first:
proc sql noprint;
select distinct quote(task_name) into :task_list separated by ", "
from have
where email like '%@clinical.com';
quit;
%put &task_list;
If the macro variable is too long you'll need a different approach.
Could you
Suggestion would be to cut the example data by maybe half.
Then show the desired result as well, which should be easier with the reduced set.
Reason for showing the desired result is the phrase "has the values that associated with the clinical.com email" is not exactly clear.
Does this mean that you want to do something with the "task_name" variable? Get the user name from such as tcollins from the string tcollins@clinical.com or something else.
Since each record could only have 1 "blank" what you actually want to count appears to be something else, possibly a summary after this data set is created.
I just edited my post.
this is a sample data has only one blank but my real data has more
Why not use FIND/INDEX instead of SCAN, since you're searching for a specific string set.
if find('@clincial.com', assigned_to, 'i')>0 then result=task_name;
Isn't that all you need?
IF/THEN/DO.
Adding on to my previous answer, change it to conditional so that you can add the count. You only show one value of 1, so not sure why the rest are blank or if you want them incremented or something. If so, use RETAIN. If you cannot get it working at this point, post your code.
if <condition> then do;
<multiple sas statements>;
count=1;
end;
Please be much more clear in your initial question.
@mona4u wrote:
I know if then do
the question is
if then always need to specify a value of the variable to subset
I want to specify the variable that has all those values but without mentioning the values
I have no idea what that means.
Yeah, still not following. Show me 5 lines of data and exactly what you want as output - as two different data sets.
@mona4u wrote:
I mean usually, we subset this way
if var=a and var1=" " then count
if var in ("a", "b") and var1=" " then count
but the code that I need is
if the value in the var then count without specifying the value of the variable
dataset
Task_Name | Assigned_To |
Admin QC | tcollins@clinical.com |
Image Preview | vpandya@clinical.com |
Admin QC | tcollins@clinical.com |
Image Preview | tcollins@clinical.com |
Admin QC | tcollins@clinical.com |
Image Preview | vpandya@clinical.com |
Photo Image Preview QC | tcollins@clinical.com |
Admin QC | araju@clinical.com |
Image Preview | arodriguez@clinical.com |
Admin QC | tcollins@clinical.com |
Image Preview | tcollins@clinical.com |
Admin QC | vpandya@clinical.com |
Image Preview | tcollins@clinical.com |
Admin QC | araju@clinical.com |
Image Preview | arodriguez@clinical.com |
Admin QC | marnofsky@wcclinical.com |
Image Preview | tcollins@clinical.com |
Admin QC | vpandya@clinical.com |
Image Preview | tcollins@clinical.com |
Admin QC | tcollins@clinical.com |
Image Preview | tcollins@clinical.com |
Admin QC | vpandya@clinical.com |
Image Preview | tcollins@clinical.com |
Admin QC | araju@clinical.com |
Image Preview | arodriguez@clinical.com |
Admin QC | |
Admin QC | marnofsky@clinical.com |
Image Preview | tcollins@clinical.com |
irRC RECIST Oncology | rbht@bidmc.harvard.edu |
Post Pre-Derm QC | cwalsh@clinical.com |
Post Review QC 1 | araju@clinical.com |
Post Review QC 2 | araju@clinical.com |
Pre-Dermatology | cwalsh@clinical.com |
Radiology Review 1 | csado@partners.org |
Radiology Review 2 | vassah@hotmail.com |
irRC RECIST Oncology |
result I desire
Task_Name | Assigned_To | result | count |
Admin QC | tcollins@clinical.com | Admin QC | |
Image Preview | vpandya@clinical.com | Image Preview | |
Admin QC | tcollins@clinical.com | Admin QC | |
Image Preview | tcollins@clinical.com | Image Preview | |
Admin QC | tcollins@clinical.com | Admin QC | |
Image Preview | vpandya@clinical.com | Image Preview | |
Photo Image Preview QC | tcollins@clinical.com | Photo Image Preview QC | |
Admin QC | araju@clinical.com | Admin QC | |
Image Preview | arodriguez@clinical.com | Image Preview | |
Admin QC | tcollins@clinical.com | Admin QC | |
Image Preview | tcollins@clinical.com | Image Preview | |
Admin QC | vpandya@clinical.com | Admin QC | |
Image Preview | tcollins@clinical.com | Image Preview | |
Admin QC | araju@clinical.com | Admin QC | |
Image Preview | arodriguez@clinical.com | Image Preview | |
Admin QC | marnofsky@wcclinical.com | Admin QC | |
Image Preview | tcollins@clinical.com | Image Preview | |
Admin QC | vpandya@clinical.com | Admin QC | |
Image Preview | tcollins@clinical.com | Image Preview | |
Admin QC | tcollins@clinical.com | Admin QC | |
Image Preview | tcollins@clinical.com | Image Preview | |
Admin QC | vpandya@clinical.com | Admin QC | |
Image Preview | tcollins@clinical.com | Image Preview | |
Admin QC | araju@clinical.com | Admin QC | |
Image Preview | arodriguez@clinical.com | Image Preview | |
Admin QC | 1` | ||
Admin QC | marnofsky@clinical.com | Admin QC | |
Image Preview | tcollins@clinical.com | Image Preview | |
irRC RECIST Oncology | rbht@bidmc.harvard.edu | ||
Post Pre-Derm QC | cwalsh@clinical.com | Post Pre-Derm QC | |
Post Review QC 1 | araju@clinical.com | Post Review QC 1 | |
Post Review QC 2 | araju@clinical.com | Post Review QC 2 | |
Pre-Dermatology | cwalsh@clinical.com | Pre-Dermatology | |
Radiology Review 1 | csado@partners.org | ||
Radiology Review 2 | vassah@hotmail.com | ||
irRC RECIST Oncology |
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.