About A_Kh

ballardw · ‎04-10-2023

@maroulator1 wrote: Is it then fair to say that from the variables Var1 - Var5 in my dataset, if I only wanted to make changes to 3 of them, say Var1, Var3, and Var5 there is no way to do so other than putting Var1, Var3, and Var 5 into an array? Not at all. What the ARRAY syntax allows is quick flexibility. If you do something like Array v (*) var1 var3 var5; do i= 1 to dim(v); v[i]=sum(v[i], 3000); end; is equivalent to writing var1= sum(var1,3000); var3= sum(var3,3000); var5= sum(var5,3000); The flexibility of an array based approach is that consider your next data set you need to do similar has 15 variables. It is much easier to just replace the list in the Array definition with the "new" list of 15 variable names. None of the other code would need to change. However if using the the Var1= statements you would need to rewrite/add 15 statements. And then the time after that with 8 variable for example you would need to remove statements and make the variables match the new list. You could even use something like array V(*) var1-var5; do i=1 to dim(v); if i in (1, 3, 5) then v[i] = sum(v[i], 3000); end; The IN operator returns true when i is any of the values in the list (note: not variables). This would only do the addition with the index variable is the value of the element in the array that you want to modify. The STYLE choice of which code to use could depend. The last would be a better solution if you know that the variables will stay constant but that the variables that need that adjustment change frequently. If you search this forum long enough I believe you will find a few questions/solutions involving arrays with 1000 or more variables. Another use of the Arrays might be that you need to adjust the values differently for different variables. That might lead to a temporary array with the values used for the adjustment and code that looks like: Array v (*) var1 var3 var5; do i= 1 to dim(v); v[i]=sum(v[i], temp[i]); end; Where Temp has been properly defined to hold the constant values needed to adjust each variable.

PrinceAde · ‎04-05-2023

Thank you, sir. It is a class work, I will research the %tableN. Thanks for your response.

PaigeMiller · ‎04-03-2023

Remove the comma which was accidentally placed at the end of the link.

A_Kh · ‎03-27-2023

@Kathryn_SAS , Awesome! This solved my problem, thank you so much!

A_Kh · ‎03-14-2023

Dear Community, Just wanted to follow up on this question as I received the solution from SAS Tech Support Team. The solution was pretty simple - adding an extra numeric variable to the dataset HAVE (order=_n_) then define this variable in proc report as internal ordering variable (define order / order order=internal noprint;). The full code is below: data have; set have; order=_n_; run; ods tagsets.excelxp file = "C:\Users\Desktop\test\Score_tables.xml" style=listing options(sheet_name= "Table 3 - Categorical" embedded_titles = "on" embedded_footnotes = "on" Merge_Titles_Footnotes = "on" missing_align= 'center' Row_Heights='24,16,0,32,32,0,0' Skip_Space= '0,0,0,1,1'); proc report data= have style(report)={background=white borderrightcolor=white borderleftcolor=white bordertopcolor=black borderbottomcolor=black bordertopwidth=1pt borderbottomwidth=1pt} style(header)={height=24pt font_face='TREBUCHET MS' fontsize=11pt background=white borderrightcolor=white borderleftcolor=white bordertopcolor=black borderbottomcolor=black bordertopwidth=1 borderbottomwidth=1} ; column order label N cat1 cat2 cat3; define order / order order=internal noprint; define Label/ " " width=25 order ; define N/ "Total" center width=8 style(column)= {just=c font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt} display; define cat1/ "Excellent" center width=8 style(column)= {just=c font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt} display; define cat2/ "Good" center width=8 style(column)= {just=c font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt} display; define cat3/ "Fair" center width=8 style(column)= {just=c font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt} display; compute label; if strip(label) eq "Change_from_Baseline" then call define(_col_, "style", "style=[indent=3 just=l font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt]"); else if strip(label) in ("Female", "Male") then call define(_col_, "style", "style=[indent=6 just=l font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt]"); else call define(_col_, "style", "style=[just=l font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt]"); endcomp; run; ods tagsets.excelxp close; The obtained result is: Hope this will be helpful to others too. Thank you, all, for taking time to answer the question!

A_Kh · ‎03-04-2023

@ballardw, Thank you for your feedback. Below if my code from Proc report and Data (simulated) that i'm working on. I was using define VISIT/ group statement. But order=data option is what I'm looking for. It really does the job the way I expected. Thank you! @Kurt_Bremser, I like the approach you suggested, but the above way seems more efficient in my situation. I appreciate your time and support! @Shaik_Adil, I'm providing the data and codes below as an answer to your question. Thank you for checking in with me! Again, thank you all for spending time to read and answer my post! data have; length Visit $25; input VISIT $ _n _mean _std ; datalines; Week12 210 0.55 0.84 Month8 150 0.05 0.73 Month12 120 0.07 0.51 Month16 135 0.33 0.65 Month20 101 0.06 0.43 Month24 256 0.35 0.71 Month28 175 0.11 0.43 ; run; ods tagsets.excelxp file = "C:\Users\Desktop\test\Score_tables.xml" style=listing options(sheet_name= "Table 3 - Categorical" embedded_titles = "on" embedded_footnotes = "on" Merge_Titles_Footnotes = "on" missing_align= 'center' Row_Heights='24,16,0,32,32,0,0' Skip_Space= '0,0,0,1,1'); proc report data= have spanrows style(report)={background=white borderrightcolor=white borderleftcolor=white bordertopcolor=black borderbottomcolor=black bordertopwidth=1pt borderbottomwidth=1pt} style(header)={height=24pt font_face='TREBUCHET MS' fontsize=11pt background=white borderrightcolor=white borderleftcolor=white bordertopcolor=black borderbottomcolor=black bordertopwidth=1 borderbottomwidth=1} out=report_scorechange; column visit _n _mean _std; define visit/ "VISIT" width=25 style(header)= {just=l} style(column)= {just=l font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt} group; define _n/ "n" center width=8 style(column)= {just=c font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt} display; define _mean/ "Mean" center width=8 style(column)= {just=c font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt} display; define _std/ "SD" center width=8 style(column)= {just=c font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt} display; compute before visit/ style = {indent=2 just=l font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt}; Line @3 Text $50.; length text $50; if strip(visit) eq "Week12" then text= "Reference Timepoint"; else text= "Change from Week 12"; endcomp; run; ods tagsets.excelxp close;

A_Kh · ‎02-21-2023

@PaigeMiller , Thank you for your input! I should have specified that was tagsets.excelxp destination, not excel itself. Unsure about how much different are options for those two destinations, I applied format 4.2 to the column in define statement(define Mean/ "Mean" center width=8 format=4.2 display;) , which rounded up the last decimal in xml file.. ods tagsets.excelxp options (sheet_name= "Table 7." start_at= "B3" embedded_titles = "on" embedded_footnotes = "on" Merge_Titles_Footnotes = "on" Row_Heights='0,0,0,32,32,0,0' Skip_Space= '0,0,0,0,1'); proc report data= items_final style(report)={background=white borderbottomcolor=black borderbottomwidth=1} style(header)={background=white borderrightcolor=white borderleftcolor=white bordertopcolor=black borderbottomcolor=black bordertopwidth=1 borderbottomwidth=1}; column ITEM LABEL N MEAN SD cat0 cat1 cat2 cat3 cat4 cat5 Missing; define ITEM/ "Item ID" left width=10 display; define LABEL/ "Item Content" left width=30 display; define n/ "Valid n" center width=8 display; define Mean/ "Mean" center width=8 Style(column)={TAGATTR='format:0.00'} display; define SD/ "SD" center width=8 Style(column)={TAGATTR='format:0.00'} display; define cat0/ "0" center width=11 display; define cat1/ "1" center width=11 display; define cat2/ "2" center width=11 display; define cat3/ "3" center width=11 display; define cat4/ "4" center width=11 display; define cat5/ "5" center width=11 display; define Missing/ "Missing" center width=11 display; run; title;footnote;

Tom · ‎01-31-2023

@A_Kh wrote: Thank you @Tom and @Kurt_Bremser ! I appreciate your support, this way of reading data in SAS is something fundamental that I should learn. @Tom , regarding the second part of the code, each file (31 files) is located in a separate folder with a different names. Only common part is .txt extension for all files(different file names as well). Therefore, I've hardcoded the list of files in earlier steps into &file1-&file31.. This part is something I could handle by myself, but thank you so much for your input! Note that it is easier to just put the list of names into a DATASET instead of bothering to try to figure out how to use the macro language to generate code. data files; infile cards truncover ; input filename $256. ; cards; filename1.txt filename2.txt ; data want; set files; filevar=filename; infile dummy firstobs=2 truncover filevar=filevar end=eof; do while (not eof); input .... output; end; run; You could even combine the two data steps into one if you wanted.

SASKiwi · ‎01-24-2023

OK, that confirms that you have no SAS/ACCESS product either installed or licensed. You will need to follow @Morse_Bill 's advice to enable SAS to read SQL Server databases.

Quentin · ‎11-04-2022

This hack can also be used as a custom version of MCOMPILENOTE. So in mymacro.sas you have: %put Compiling macro MyMacro version 1.5; %macro mymacro; ... %mend; You'll get that note in the log when it is compiled. You can have version number, a path to the .sas file, author info, etc. Some people like to write macros with a help parameter that writes instructions to the log. This approach could be used to write instructions to the log when the macro is compiled. Also could be used for people who want to track how often different autocall macros are 'used', since one definition of used could be the number of sessions where the macro was compiled. I think I've got one or two macros where I did the multiple macros in one file thing. While it felt a little sleazy, it never caused a problem. Other than of course a month later when I read through the main macro and see a reference to %helper, I usually waste 5 minutes trying to find the helper.sas until I remember that it's defined in the same file. So there's that. : )

Ksharp · ‎04-13-2022

Follow the @Tom suggestion . There is another macro can do this . Same as filename xpt 'Z:\Databases\CDM\Exports\Production\20220408_Test\Export\Results\xyz.xpt'; libname sas v9 'Z:\Databases\CDM\Exports\Production\20220408_Test\Export\Input'; %xpt2loc(libref=sas,filespec=xpt)

A_Kh · ‎03-15-2022

Hi Patrick, Thank you so much, it was so simple and worked perfectly!

FreelanceReinh · ‎09-02-2021

Here is a variant of my previous suggestion using SASHELP.VCOLUMN instead of a dataset VARLIST created in a separate step using CALL EXECUTE, thus obviating the need for a temporary code file allowing for non-standard variable names inserting only '0A'x (instead of '0D0A'x) between the letters of the variable name in the label, as this is sufficient to obtain the vertical display in SAS Universal Viewer ('0D'x works as well). /* Create a new sample dataset */ options validvarname=any; data have2; input SUBJID $ AGE GENDER $ 'this is a 32-char. variable name'n; cards; 001 42 F 9 ; /* Add the variable names as "vertical" labels */ %let ds=work.have2; data _null_; length ren $165; set sashelp.vcolumn(where=(libname="%upcase(%scan(&ds,1,.))" & memname="%upcase(%scan(&ds,2,.))")) end=last; if _n_=1 then call execute("proc datasets lib=%scan(&ds,1,.) nolist; modify %scan(&ds,2,.); label"); ren=cats(nliteral(name),'="'); w=length(name); do i=1 to w; ren=catt(ren,put(char(name,i),$hex2.)); if i<w then ren=catt(ren,'0A'); end; ren=catt(ren,'"x'); call execute(ren); if last then call execute('; quit;'); run;

ChrisNZ · ‎08-04-2021

I see this gives required attributes as format and length, not "the formatted value" which is "0", "1" and "2". You want a length of $2. Therefore the unformatted value can only be 0, 1, 2 and the formatted value is the text.

jimbarbour · ‎08-02-2021

DROP is actually not executed during the run. DROP is processed at compile time. So, it really doesn't matter where you put the DROP statement in the program (as long as it is valid SAS syntax). I usually put the DROP near the top because that's typically where I put the SET, LENGTH, and DATA statements that create or bring in variables. That way I have all my variable related actions in close proximity for ease of reference. Jim

Online Status	Offline
Date Last Visited	yesterday

Re: long to wide for multiple varaibles

Re: Proc report: is it possible to define variable label by group?

Proc report: is it possible to define variable label by group?

Re: need help with a dataset filtering of records

Re: SAS programming "golf": Find the fake books

Re: SAS programming "golf": Find the fake books

Re: SAS programming "golf": Find the fake books

Re: How to Delete Entire Row if Conditions Are Met

Re: cannot search using index function

Re: Sorting pairs and singles to assign new ID

Re: Proc report: is it possible to define variable label by group?

Re: Proc report: is it possible to define variable label by group?

Re: File Name value exceeds maximum length of 201 characters - UNIX

Re: Highlight content in Proc report

Re: How create multiple records from horizontal data

Re: Proc report: is it possible to define variable label by group?

Re: SAS programming "golf": Find the fake books

Re: Sorting pairs and singles to assign new ID

Re: merge rows with condition

Re: if statements occasionally partially ignored

Re: SAS loop over variable list

Re: Table restructuring

Re: Proc report and Macro

Re: ODS LAYOUT borders

Re: Proc Report: conditionally assigning STYLE attributes to a specifi...

Re: Proc Report issues: how to prevent data from automatic ordering

Re: Proc Report to ODS EXCEL (xml) : issue in writing last 0 after the...

Re: Reading external unstructured .txt file into sas: how to read spec...

Re: How to connect SAS to SQL Server: is any initial set up process ne...

Re: Macro debugging: autocall issues

Re: PROC COPY/EXPORT engine limitations for transport files (.xpt)

Re: Conditionally concatenating multiple variables (do loop/iterative ...

Re: Variable name from horizontal to vertical in sas dataset (not in p...

Re: PROC FORMAT vs LENGTH: how to avoid data truncation?

Re: LOCF and retain statement

SAS Inner Circle Panel