About CP2

CP2 · ‎07-12-2017

Does anyone know why the countw function does not work when the words are separated by a comma but will work when separated by another character? This works: %let name = type1|type2|type3 ; %LET word_count = %sysfunc(countw(&name., %str(|) )) ; %put &word_count. ; This does not work: %let name = type1,type2,type3 ; %LET word_count = %sysfunc(countw(&name., %str(,) )) ; %put &word_count. ;

CP2 · ‎07-11-2017

Actually, i've never seen the way you added the sysfunc to the array. Could you explain the logic of adding this ("%sysfunc(tranwrd(&names,%str( )," "))"); I know what the tranwrd function is doing but I don't know how it is used with the array.

CP2 · ‎07-11-2017

Thanks. I went down that road already but I need to still transfer the content in the columns into macro variables because I then want to use those variables in the 'group by' statement of a proc sql to match to the original content on those var1-var3 variables to another database to create new variables from an aggregate. And, probably need to do the match in a loop for each 'type' value. It's a database weighting task but I think it's too complicated to explain so I'll just keep trying things out. I'll figure it out eventually. Thanks for your help though.

CP2 · ‎07-11-2017

data have; input type $ total var1 $ var2 $ var3 $ ; datalines ; small 30 a 1 x small 30 b 1 x large 7 a 2 x large 7 b 3 y large 7 c 4 y ; run; There is a value associated with each column based on a macro variable. In this case, 5 for var1, 4 for var2, and 3 for var3. I don't really care at this point what content is in the var1 to var3 columns. I just want the names of the variables in a string based on the condition that looks at variable 'total'. The 5,4,3 values come from a different macro variable that are associated with var1, var2 and var3. pseudo code: variable list, lst, should equal the string, 'var1 var2 var3' do k = 1 to 3 (loop through the number of variables) 'current' should equal kth word in the variable list: %scan(&lst., &k.) if total < 5 * 2 then want =catx(',', want, current) else other=catx(',', other, current) if total < 4 * 2 then want =catx(',', want, current) else other=catx(',', other, current) if total < 3 * 2 then want =catx(',', want, current) else other=catx(',', other, current) In the end for this example and for blue type want should equal 'var1,var2,var3' and other should be blank because total =30 for small type also, loop through the values of type variable. if total < 5 * 2 then want =catx(',', want, current) else other=catx(',', other, current) if total < 4 * 2 then want =catx(',', want, current) else other=catx(',', other, current) if total < 3 * 2 then want =catx(',', want, current) else other=catx(',', other, current) In the end for this example and for red type 'want' should equal 'var1,var2' and other should equal 'var3' because total=7 for large type

CP2 · ‎07-11-2017

I wasn't thinking of that for global token variables but that makes sense. Any idea how I can assign the variable names to a macro variable (for later use) if the data in their column doesn't/does meet a certain condition?

CP2 · ‎07-11-2017

but I need to do it in a loop so would need to combine the prior value of c in your example.

CP2 · ‎07-11-2017

I also tried this in the variable assignment: %let low_lst = %sysfunc(catx(',',&current.,&&&low_lst.)) ;

CP2 · ‎07-11-2017

Thanks Patrick. Actually you're right about creating the table. Originally I thought updating would be better but I just ended up creating a new table .

CP2 · ‎07-11-2017

Does anyone know how I can concatenate macro variables. Here is what I am working with: It is running through an entire database and if Total is less than a certain level (levels are related to a variable names in &lst) then I want to concatenate the variable names and use later. Total is a different value for all values of Type. The actual database is irrelevant; all I need are the concatenated macro variables to use in a group by statement later on. %let lst = var1 var2 var3 ; %let var_count = 3 ; DATA want; do until (last.type) ; SET have ; by type ; ARRAY level[3] (5 4 3) ; /*a macro variable will be placed here so these values will change in my bigger program*/ %let k=1 ; do k=1 to &var_count. ; %LET current=%scan(&lst.,&k.); IF total < (level[k] * 2) THEN DO; %LET low_lst = &current.||&&&low_lst. ; END ; ELSE %Let Oth_lst= &current.||&&&oth_lst. ; %put &oth_lst. ; end ; end ; output ; run;

CP2 · ‎07-07-2017

I am having trouble with the update statement using a join to get create a calculation. I need to use an innerjoin to get an aggregate value from one database to create a new calculated field. This needs to be done to the same file to create three new fields (in this example) but the number of variables and names of variables will differ so I am using macro variables. I have the proc sql set up in a loop so rather than overwrite the file with a create table I want to use UPDATE instead. At first I was using the same file in the update statement as I was in the inner join but was getting an error as if I was trying to edit an open file. I thought this had something to do with the loop so I created a subset from the full table as the one I want to update (I have to subset anyway).. The following is a simple example that is getting the distribution of each variable across the combinations of the other variables. For example I want to answer: what is the distribution of all colors WITHIN LIKE-COMBINATIONS of other variables? Do you see any syntax error that stands out ? I keep getting an error and I tried an alias for the update table and join to the update table but I am not getting anywhere. I know this is complicated so I included the sample files below. OPTIONS NOQUOTELENMAX MPRINT ; %LET list = shade color size ; %LET numVars = 3 ; %macro test() ; %do i=1 %to &numVars ; /*loop through the three grouping variables */ %let current=%scan(&list.,&i); %let remaining=%sysfunc(tranwrd(%sysfunc(compbl(%sysfunc(tranwrd(&list.,%sysfunc(trim(&current.)),%str())))),%str( ),%str(,))); PROC SQL ; ALTER TABLE subset ADD &current.New FLOAT ; /*add a new column to place calculated field*/ UPDATE subset SET &current.New = (select a.value/b.sum from FullTable as a INNER JOIN (select type, id, &remaining., sum(value) as sum from FullTable where type = 1 group by id, type ) as b ON a.id = b.id and a.type=b.type %DO n = 1 %TO &numVars.-1; AND a.%SYSFUNC(COMPRESS(%SCAN(%bquote(&remaining.), &n., ','),%str(,))) = b.%SYSFUNC(COMPRESS(%SCAN(%bquote(&remaining.), &n.,','),%str(,))) %END ; ) ; QUIT; %end; %mend test; %test(); sample datasets: data FullTable; input id $ shade $ color $ size $ value type ; datalines ; 1 light blue small 5 1 1 medium blue small 3 1 1 dark blue small 2 1 2 light red large 10 1 2 medium red large 1 1 3 light red large 4 0 3 medium green xsmall 2 0 3 dark green xsmall 9 0 4 light blue small 2 0 4 medium blue small 5 0 4 dark blue small 5 0 5 light red large 4 1 5 medium red large 6 1 6 medium red large 2 0 7 light red large 1 1 7 medium green xsmall 8 1 7 dark green xsmall 6 1 8 light red large 6 1 8 light blue large 9 1 9 dark green small 10 2 9 dark green xsmall 11 2 9 dark green large 12 2 ; run; /*subsetTable is a subset of FullTable - Type = 1 */ data subset; input id $ shade $ color $ size $ value type ; datalines ; 1 light blue small 5 1 1 medium blue small 3 1 1 dark blue small 2 1 2 light red large 10 1 2 medium red large 1 1 5 light red large 4 1 5 medium red large 6 1 7 light red large 1 1 7 medium green xsmall 8 1 7 dark green xsmall 6 1 8 light red large 6 1 8 light blue large 9 1 ; run; data WANT; input id $ shade $ color $ size $ value type shadeNew colorNew sizeNew ; datalines ; 1 light blue small 5 1 0.5 1 1 1 medium blue small 3 1 0.3 1 1 1 dark blue small 2 1 0.2 1 1 2 light red large 10 1 0.909090909090909 1 1 2 medium red large 1 1 0.0909090909090909 1 1 5 light red large 4 1 0.4 1 1 5 medium red large 6 1 0.6 1 1 7 light red large 1 1 1 1 1 7 medium green xsmall 8 1 0.571428571428571 1 1 7 dark green xsmall 6 1 0.428571428571429 1 1 8 light red large 6 1 1 0.4 1 8 light blue large 9 1 1 0.6 1 ; run;

CP2 · ‎06-07-2017

The 'have' sample dataset was originally created from a proc freq using ods output to a dataset. I appreciate you helping me to understand the proc report better but it looks like I'd be better off doing my reporting in Excel. However, just so I don't leave you hanging, this is what I want to end up with (created in Excel from the SAS 'have' dataset in my example). Combination Group Combination Percent Dar Mono daratumumab 100 IMiD Mono lenalidomide 94.29 thalidomide 4.29 pomalidomide 1.43 PI Mono bortezomib 93.88 carfilzomib 5.94 other 0.17 PI-Cyclo± bortezomib - cyclophosphamide 99.25 carfilzomib - cyclophosphamide 0.75 PI-Dox± bortezomib - doxorubicin lipos 100 PI-IMiD bortezomib - lenalidomide 98.36 bortezomib - pomalidomide 1.09 other 0.55 Other investigational drugs 34.48 bortezomib - daratumumab 17.24 elotuzumab 13.79 cyclophosphamide 10.34 elotuzumab - lenalidomide 6.9 other 17.25

CP2 · ‎06-07-2017

Cynthia, Thanks for taking a look at this. There was a step I didn't identify which might explain the 'other' issue. There will always be an OTHER combogroup (DRUGSORT=1 IF COMBOGROUP=OTHER OR ELSE IT IS 0). Within each combogroup category, including this OTHER combo group, there are many drugs. The percentages should add to 100% within each combogroup. We only want to show the drugs that most contribute to the category so drug names have been changed to "other" if the share is small with the intent of collapsing the 'other' category and summing the percents for that group. This means that there will even be 'other' drugcombinationchemo within the OTHER combogroup. So, if I add drugsort2 to the columns and define it as an ORDER option I will get "Other" combogroup and "other" drugcombinationchemo showing up at the end of the table. This is ok but the problem is I really want the percents to be in descending order WITHIN combogroup too. For example the following code gets me there ALMOST. If you run it you will see that for the IMiD Mono category pomalidomide is only 1% and is listed before thalidomide which is 4%. If I have to choose between "other" being at the end of each category OR values be in descending order, I'd rather have the values in descending order. The other problem which can be fixed with a proc tabulate is that the multiple 'others' need to be summed and collapsed into one row within each combogroup category. I was hoping this could be done in the proc report to avoid unnecessary multiple steps but maybe it's not possible ??? What I am noticing is that it only shows one of the 'other' rows in the category and blanks out the others. Your sort order is not in the right order and I believe this is because drugsort needs to be before drugsort2. If this helps identify my problem better and you think of anything please pass it along. Thanks! proc report data=have nowd style(report)=[background=ltgray ] style(column)=[background=gwh] style(header)=[background=ltgray foreground=white]; column drugsort combogroup drugsort2 drugcombinationchemo percent ; define drugsort /order order=internal noprint; define drugsort2 /order order=internal noprint; define combogroup /order order=data group 'Combination Group' ; define percent /analysis sum order order=internal; define drugcombinationchemo /group 'Combination' ; run;

CP2 · ‎06-07-2017

hello, I'm having trouble sorting and collapsing my data in a proc report. I have created a sample dataset. Prior programming has replaced any frequency percent <3.9% as 'other' in the drugcombination variable. In the report, I want to do the following: 1) to collapse the 'other' category within each combogroup. 2) to place 'Other' combogroup at the end and 'other' drugcombinations at the end within combogroup. I believe I have done this with the dummy sort variables I have created. 3) I want to put the drugcombinationchemo in an order based on the descending value of the percent column (while still keeping 'other' at the end if possible). I have tried various ways to show the percent column in descending order plus collapsing 'other' and nothing seems to work. Do I need to do a proc tabulate to sum the 'other' values and then do proc report? I am getting the following output which is not accomplishing item 1 and item 3 above. SAS Output Combination Group Combination Percent Dar Mono daratumumab 100.00 IMiD Mono lenalidomide 94.29 pomalidomide 1.43 thalidomide 4.29 PI Mono bortezomib 93.88 carfilzomib 5.94 other 0.17 PI-Cyclo± bortezomib - cyclophosphamide 99.25 carfilzomib - cyclophosphamide 0.75 PI-Dox± bortezomib - doxorubicin lipos 100.00 PI-IMiD bortezomib - lenalidomide 98.36 bortezomib - pomalidomide 1.09 other 0.55 Other bortezomib - daratumumab 17.24 cyclophosphamide 10.34 elotuzumab 13.79 elotuzumab - lenalidomide 6.90 investigational drugs 34.48 other 3.45 code: DATA have ; infile datalines delimiter=',' ; format combogroup $12. DrugCombinationChemo $30. Percent 8.2 drugsort 8.0 drugsort2 8.0 ; input combogroup $ DrugCombinationChemo $ Percent drugsort drugsort2 ; datalines ; Dar Mono,daratumumab,100,0,0 IMiD Mono,lenalidomide,94.2857142857143,0,0 IMiD Mono,thalidomide,4.28571428571429,0,0 IMiD Mono,pomalidomide,1.42857142857143,0,0 Other,investigational drugs,34.4827586206897,1,0 Other,bortezomib - daratumumab,17.2413793103448,1,0 Other,elotuzumab,13.7931034482759,1,0 Other,cyclophosphamide,10.3448275862069,1,0 Other,elotuzumab - lenalidomide,6.89655172413793,1,0 Other,other,3.44827586206897,1,1 Other,other,3.44827586206897,1,1 Other,other,3.44827586206897,1,1 Other,other,3.44827586206897,1,1 Other,other,3.44827586206897,1,1 PI Mono,bortezomib,93.8811188811189,0,0 PI Mono,carfilzomib,5.94405594405594,0,0 PI Mono,other,0.174825174825175,0,1 PI-Cyclo±,bortezomib - cyclophosphamide,99.2481203007519,0,0 PI-Cyclo±,carfilzomib - cyclophosphamide,0.75187969924812,0,0 PI-Dox±,bortezomib - doxorubicin liposomal,100,0,0 PI-IMiD,bortezomib - lenalidomide,98.3606557377049,0,0 PI-IMiD,bortezomib - pomalidomide,1.09289617486339,0,0 PI-IMiD,other,0.546448087431694,0,1 ; run; proc print; run; proc sort data=have ; by drugsort combogroup drugsort2 descending percent ; run; proc print; run; proc report data=have nowd style(report)=[background=ltgray ] style(column)=[background=gwh] style(header)=[background=ltgray foreground=white]; column drugsort combogroup drugcombinationchemo percent ; define drugsort /order order=internal noprint; define combogroup /order order=data group 'Combination Group' ; define percent /analysis sum order order=internal; define drugcombinationchemo /group 'Combination' ; run;

CP2 · ‎06-06-2017

thank you!

CP2 · ‎06-06-2017

Hi, This is probably a very simple question but I can't seem to output the data from a proc freq to a dataset. I am using the out= option but I am only gettng the count and percent. I want to also include the cumulative percent. Is this possible?

Online Status	Offline
Date Last Visited	‎06-18-2024 08:04 PM

Identify first occurrence based on different keys

Re: how to fix this PROC MI MICRO problem

Re: sgplot color control for multiple series with group=

Re: sgplot color control for multiple series with group=

Re: sgplot color control for multiple series with group=

sgplot color control for multiple series with group=

Re: looping through a list to create small dataset

looping through a list to create small dataset

Re: looping through dates

Re: looping through dates

Re: resolving macro

Re: CountW function

Re: concatenate macros in a loop

Re: Using Like operator as macro value in proc sql

Re: scan function

Re: SUMIF with multiple conditions

CountW function

Re: concatenate macros in a loop

Re: concatenate macros in a loop

Re: concatenate macros in a loop

Re: concatenate macros in a loop

Re: concatenate macros in a loop

Re: concatenate macros in a loop

Re: update PROC SQL

concatenate macros in a loop

update PROC SQL

Re: proc report

Re: proc report

proc report

Re: proc freq output dataset

proc freq output dataset