Wenn klar ist, dass es mindestens eine Spalte _NAME_ gibt, dann kann's auch gleich mit dem Datastep weiter gehen: data ds1; length _NAME_ $10; run; data ds2; set ds1; ARRAY all _ALL_; if dim(all)=1 then put 'WARNING: Stopping operations'; else put 'NOTE: proceeding further steps'; run;   ... View more

That's exactly what I do 🤗 and meet the requirement 🎉 I conditionally insert values in a dataset with PROC SQL ... View more

Hi, there is a way... PROC SQL ; CREATE VIEW CLASS AS SELECT monotonic() AS _N_,* FROM sashelp.class ; CREATE VIEW class_hlp AS SELECT monotonic()+.1 AS _N_, 'Anand' AS name, 'M' AS sex, 16 AS age, 8.11 AS Height, 54.22 AS Weight FROM CLASS ; CREATE TABLE want(DROP=_N_) AS SELECT * FROM CLASS UNION CORRESPONDING ALL SELECT * FROM class_hlp WHERE (mod(int(_N_), 2)=0) ORDER BY _N_ ; DROP VIEW class, class_hlp; QUIT; ... View more

Hi, not really clear to me what you want to achieve. Here's my best try using your description PROC FORMAT; VALUE Side 0='no value' 1='left' 2='right' ; VALUE var1_ 0='no value' 1='one sided left' 2='one sided right' 3='both sides' 4='one sided left and no value' 5='one sided right and no value' 6='both sides and no value' ; run; DATA have; INPUT id subid side var1; FORMAT side side. var1 var1_.; DATALINES; 1 1 1 3 1 2 2 3 1 3 2 3 2 1 2 5 2 2 0 5 3 1 1 4 3 2 0 4 3 3 1 4 3 4 1 4 3 5 1 4 4 1 0 0 5 1 1 1 ; RUN; PROC SQL; CREATE TABLE want AS SELECT DISTINCT side,var1 FROM have ORDER BY side,var1 ; QUIT; ... View more

Hi,   I've tested it exactly as you wrote it, it works like a charm. My hypothesis are:  - your admin restricted the use of vbs files - The drive H is not resolved correctly, try to use the real Path where H is mounted to ... View more

Hi, Excel sheets can be created like this: Proc Export data=sashelp.class outfile="C:\Temp\yourFile.xlsx" dbms=EXCEL Replace; sheet='Class'; run; Simply repeat the operation for several sheets ... View more

Hi, simply use the macro variable that refers to the xlsx file in double quotes: LIBNAME xl XLSX "&yourPromptPath..xlsx"; ... View more

Hi, do not use SAS (english) but "SAS with unicode support" ... View more

easy 😅 thanks ... View more

Hi, while performing the calculation in a datastep, I realized it became quickly diffcult to read, so I did the same using a macro step. There must be an easier way I can't think of right now   %** Create test data; DATA have; length person $10 reqyear pm25_total2000-pm25_total2018 8; array pm pm25_total2000-pm25_total2018; do i=1 to 10; person=cats('Person',put(i,best.)); do over pm; pm=int(ranuni(0)*100); end; reqyear=2000 + floor((1+2018-2000)*rand("uniform")); output; end; drop i; RUN; %** 1 - Using datastep; DATA want; set _NULL_; RUN; DATA _NULL_; length pmname pm_start pm_end $32; SET have; ARRAY PMS pm25_total2000-pm25_total2018; do i=1 to dim(PMS); pmname =strip(upcase(vname(PMS[i]))); pm_start =strip(upcase(cats('pm25_total',put(reqyear-9,best.)))); pm_end =strip(upcase(cats('pm25_total',put(reqyear,best.)))); end; put 'N' 'OTE:' Person= reqyear= pm_start= pm_end=; if pm_start GE vname(PMS[1]) then do; call execute('DATA _hlp; LENGTH mean 8 analyzed $70; SET have(FIRSTOBS='||strip(put(_N_,best.))||' OBS='||strip(put(_N_,best.))||');'); call execute('ARRAY pms_mean {*} '||strip(pm_start)||'--'||strip(pm_end)||';'); call execute('mean=mean(of pms_mean[*]); analyzed="mean of '||strip(pm_start)||'--'||strip(pm_end)||'";'); call execute('RUN;'); call execute('DATA want; SET want _hlp; RUN; PROC DATASETS lib=work nolist; delete _:; RUN;QUIT;'); end; else do; call execute('DATA _hlp; LENGTH mean 8 analyzed $70; SET have(FIRSTOBS='||strip(put(_N_,best.))||' OBS='||strip(put(_N_,best.))||');'); call execute('mean=.; analyzed="mean since '||strip(pm_start)||' not calculable";'); call execute('RUN;'); call execute('DATA want; SET want _hlp; RUN; PROC DATASETS lib=work nolist; delete _:; RUN;QUIT;'); end; RUN; %** 2 - Using Macro; DATA want2; set _NULL_; RUN; %macro calcMean(inDS=,outDS=,n=,Miss=,startVar=,endVar=); %if &Miss. ne 1 %then %do; DATA _hlp; LENGTH mean 8 analyzed $70; SET have(FIRSTOBS=&n. OBS=&n.); ARRAY pms_mean {*} &startVar.--&endVar.; mean=mean(of pms_mean[*]); analyzed="Mean of &startVar.--&endVar."; RUN; %end; %else %do; DATA _hlp; LENGTH mean 8 analyzed $70; SET have(FIRSTOBS=&n. OBS=&n.); mean=.; analyzed="Mean since &startVar. not calculable"; RUN; %end; DATA &outDS.; SET &outDS. _hlp; RUN; PROC DATASETS lib=work nolist; delete _:; RUN;QUIT; %mend calcMean; DATA _NULL_; length pmname pm_start pm_end $32; SET have; ARRAY PMS pm25_total2000-pm25_total2018; do i=1 to dim(PMS); pmname =strip(upcase(vname(PMS[i]))); pm_start =strip(upcase(cats('pm25_total',put(reqyear-9,best.)))); pm_end =strip(upcase(cats('pm25_total',put(reqyear,best.)))); end; put 'N' 'OTE:' Person= reqyear= pm_start= pm_end=; if pm_start GE vname(PMS[1]) then call execute('%nrstr(%calcMean(inDS=want,outDS=want2,n='||strip(put(_N_,best.))||' ,startVar='||strip(pm_start)||',endVar='||strip(pm_end)||');)'); else call execute('%nrstr(%calcMean(inDS=want,outDS=want2,n='||strip(put(_N_,best.))||',Miss=1,startVar='||strip(pm_start)||',endVar='||strip(pm_end)||');)'); RUN; ... View more

Hi, yep a comma is missing and you could light your code up DATA master1; input ABC_WHITE $ ABC_BLACK $ ABC_YELLOW $ ABC_RED $ ACS_OTHER $; datalines; 0 0 0 0 0 1 0 0 0 0 0 1 0 0 1 0 0 1 0 0 0 0 0 1 0 0 0 0 0 1 1 1 1 1 1 ; run; DATA master1_color; LENGTH color_new $50; SET master1; ARRAY r _CHARACTER_; do over r; if r = '1' then color_new = catx('/',color_new,scan(vname(r),-1,'_')); end; RUN;   ... View more

Hi, common source of issue when data providers do not use a suitable database system. Either you proceed as @Kurt_Bremser said, converting to *.csv where format are kept or you import as you did and perform the correct date conversion afterwards. Excel do not use the same start time reference value as SAS, so you have to convert by yourself.   YOUR_DTC=put(input(XLSX_COLUMN_NAME,best32.)-21916,e8601da.); so you get "2023-01-23" ... View more