About A_Kh

A_Kh · ‎05-16-2023

Looks like missing values are captured as '.' instead of blanks. There might be hidden special characters as well. I've added STRIP and COMPRESS functions to eliminate '.'(dots) and hidden blanks Data test2; set have; array values (*) email_1-email_5; length Email $ 100 ; email= compress(email_1, '. '); do i=2 to dim(values); if strip(values{i}) ne '.' then Values{i}= strip(values{i}); else Values{i}= ''; if compress(values{i}, '. ') ne compress(values{1}, '. ') then Email= catx('/',Email,values{i}); end; drop i; proc print;run;

A_Kh · ‎05-16-2023

This code needs a little modification: Data test2; set test; array values (*) email_1-email_5; length Email $ 100 ; Email= email_1; do i=2 to dim(values); if values{i} ne values{1} then Email= catx('/',Email,values{i}); end; drop i; run;

A_Kh · ‎05-16-2023

This question is discussed in the following threads before, please check: https://communities.sas.com/t5/SAS-Programming/Convert-all-XLS-files-in-directory-to-CSV-using-SAS/td-p/875494 https://communities.sas.com/t5/SAS-Programming/Get-names-of-all-excel-files-from-multiple-folders/td-p/871953 Here you will use Windows command/path to the main folders(which includes subfolders)/file extension (.xls) to read all files regardless the name and subfolder location of the file.

A_Kh · ‎05-15-2023

The simplest way, using data step character functions. e.g. data have; var= 'ALPHA(124) BETA (5678) CHARLIE(9012) DELTA(3456)'; var1= compress(var, '', 'a'); call symputx('num', countc(var, ')')); proc print;run; data want; set have; array new [*] $ new1-new&num; do i=1 to &num; new{i}= compress(scan(var1, i, ')'), '', 'p'); if length(new{i}) le 3 then new{i}=''; end; var2= catx(',', of new:); drop i; proc print;run;

A_Kh · ‎05-15-2023

Let's say you have all these datasets in WORK library. We can use sashelp.vtable to subset datasets based on nobs. The idea is importing all datasets name with obs >0 into a macro variable for further use. Eg: data _null_; set sashelp.vtable end=eof; where libname eq 'WORK' and nobs>0; length datasets $ 200; retain datasets; datasets= catx(" ", datasets, memname); if eof then do; call symputx('list', datasets); end; run; %put &=list; data want; set &list; run;

A_Kh · ‎05-12-2023

One way to do this is you can try filename statement with windows command to read all xls files in the directory, then save file name and path in sas dataset, to import and export them in the following data step using call execute routine. The example code would be: filename xlslist pipe 'dir /b /s "C:\myfolder\*.xls" '; data temp; infile xlslist truncover; input filepath $200.; filename= scan(filepath, -1, '\'); proc print;run; data _null_; set temp end=eof; call execute("proc import datafile="||quote(filepath)||' '||"out="||scan(filename,1, '.')||' '||"dbms="||scan(filename, 2, '.')||"replace; run;"); if eof then do; call execute("proc export outfile="||quote(filepath)||' '||"data=work."||scan(filename,1, '.')||' '||"dbms=csv replace; run;"); end; run; PS: I used proc import, and call execute routine is something I rarely use, so there could be more efficient ways to write this code.

A_Kh · ‎05-11-2023

Looks like you need the variable names, that have less than 32 char.. Didn't you try the solution by @Tom, using the filter WHERE length(Column_Name) lt 32?

A_Kh · ‎05-11-2023

Hi @Tom , Does a 'column length' variable exist in the DB? like: select COLUMN_NAME, DATA_TYPE, COLUMN_LENGTH Not familiar with sql.server, just assuming if any variable exist that stores column length in it then WHERE clause could eventually pick up the required variables based on the length criteria as well. A simple example from sas library columns: proc sql; create table Columns_lt32_in_all_datasets as select distinct name, length, memname from dictionary.columns where libname eq 'SASHELP' and memname like 'A%' and length lt 32 order by 2; quit; proc print;run;

A_Kh · ‎05-11-2023

Another way is to use dictionary.columns. Advantage - you don't need to specify dataset names, only LIBRARY name is enough. It will give you all dataset names (in the directory) with the same variables. eg: proc sql; select name 'Variable_Name', count(memname) 'Count', memname 'Dataset_name' from dictionary.columns where libname eq 'WORK' group by 1 order by 1; quit; output:

A_Kh · ‎05-11-2023

Awesome! Thank you, @Kathryn_SAS once again, for the solution! I have been trying: compute after; line @1 '____________'; endcomp; which does not set the line length accordingly... A nice work around.

A_Kh · ‎05-11-2023

Addition to the above solution. data want; set t1; v2= substr(v1, 1, length(v1) - length(scan(v1, -1, '/'))-1); proc print; run; This code works for dynamic path.

A_Kh · ‎05-10-2023

data want; set have; new_var= substr(scan(var, 2, '-'), 1, length(scan(var, 2, '-'))-11); proc print;run;

A_Kh · ‎05-10-2023

Dear Community, While sending proc report output to .xml file through ods tagsets.excelxp table bottom borders not drawn. I'm using proc report style options "bordertopcolor=black borderbottomcolor=black..etc.." . Borders are present in result viewer window (html), but missing in .xml file. Couldn't find a proper documentation related to this question. Please share your knowledges. Thank you! Example code: ods tagsets.excelxp file= "C:\myfolders\Output\class.xml" options(embedded_titles='on' embedded_footnotes = "on" Merge_Titles_Footnotes = "on" Row_Heights='20,16,0,32,32,0,0' Skip_Space= '0,0,0,0,0' wrap_text='yes')style=listing; title font='TREBUCHET MS' h=11pt "Reporting Class table"; footnote j=l font='TREBUCHET MS' h=10pt 'Notes: Testing ODS tagsets.excelxp options.'; proc report data= sashelp.class 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}; where age lt 13; column sex age weight height; define sex/group style(column)={font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt}; define age/ group style(column)={font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt}; define weight/ group style(column)={font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt}; define height/ group style(column)={font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt}; run; ods tagsets.excelxp close; Html output: .xml output: Missing table bottom border

A_Kh · ‎05-09-2023

Hi @Ksharp , What an efficient way to solve the problem! Does this always return a positive value? Or is there a way(optional arguments) to control the result, like difference of first.variable from the last or vice versa...

A_Kh · ‎05-08-2023

data want; set dsin; array old time_point_3 time_point_6 time_point_9 time_point_12 time_point_18; array new tp3 tp6 tp9 tp12 tp18; array diff diff_3 diff_6 diff_9 diff_12 diff_18; do over old; new= lag(old); if old ne . and new ne . then diff=old-new; end; drop tp:; run; proc sort data=want;by pair descending type;run; data want1; set want; by pair descending type; array temp [5]; array diff [5] diff_3--diff_18; do i= 1 to 5; retain temp; if first.pair then temp{i}=diff{i}; if diff{i} eq . then diff{i}=temp{i}; end; drop temp: i; proc print;run;

Online Status	Offline
Date Last Visited	10 hours ago

Re: GTL - How to allocate more space for titles and axis labels to avo...

Re: GTL - How to allocate more space for titles and axis labels to avo...

Re: GTL - How to allocate more space for titles and axis labels to avo...

Re: GTL - How to allocate more space for titles and axis labels to avo...

GTL - How to allocate more space for titles and axis labels to avoid t...

Re: PROC SGPLOT: How to customize boxplot legends

Re: PROC SGPLOT: How to customize boxplot legends

Re: PROC SGPLOT: How to customize boxplot legends

Re: PROC SGPLOT: How to customize boxplot legends

PROC SGPLOT: How to customize boxplot legends

Re: GTL - How to allocate more space for titles and axis labels to avo...

Re: GTL - How to allocate more space for titles and axis labels to avo...

Re: GTL - How to allocate more space for titles and axis labels to avo...

Re: GTL - How to allocate more space for titles and axis labels to avo...

Re: PROC SGPLOT: How to customize boxplot legends

Re: This %Let definition runs fine but the %Macro definition does not ...

Re: long to wide for multiple varaibles

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 columns in the same dataset without duplicates

Re: merge columns in the same dataset without duplicates

Re: How to read and import files which sits under multiples folder

Re: How to extract multiple 4-digit numbers from a string and separate...

Re: SET data sets without data sets with zero rows

Re: Convert all XLS files in directory to CSV using SAS

Re: Extracting all variables which has length less than 32

Re: Extracting all variables which has length less than 32

Re: Create a dataset indicating the variables present in multiple data...

Re: ODS tagsets.excelxp doesn't print the table bottom border line in ...

Re: Programming Help

Re: extracting the words

ODS tagsets.excelxp doesn't print the table bottom border line in .xml...

Re: How will you calculate row differences within pairs for a set of v...

Re: How will you calculate row differences within pairs for a set of v...

SAS Inner Circle Panel