Hi I am working in a bank. We have a data set with scores information for every customer. Score for each customer can be any integer between 0 to 11.(11 is a failure status). I created a vector of last 12 scores for each customer(with comma between values). I have 3 questions:   Question1-Calculate How many non-missing arguments exits in the vector. For example: 7,7,9,10,9,11,11,11,11,10,11,9     Here there are 12 3,.,.,.,.,.,0,0,0,0,6,7     Here there are 7   Question2-Calculate How many missing+non-missing arguments exits in the vector. For example: 7,7,9,10,9,11,11,11,11,10,11,9     Here there are 12 3,.,.,.,.,.,0,0,0,0,6,7     Here there are 12   Question3-Calculate How many  times 11 appear For example: 7,7,9,10,9,11,11,11,11,10,11,9     Here there are 5 3,.,.,.,.,.,0,0,0,0,6,7     Here there are 0   Question4-Calculate location of first failure(11) For example: 7,7,9,10,9,11,11,11,11,10,11,9     Here we need to get:6 3,.,.,.,.,.,0,0,0,0,6,7   Here we need to get:0     Data tbl1; input ID Score1 Score2 Score3 Score4 Score5 Score6 Score7 Score8 Score9 Score10 Score11 Score12; cards; 1 7 8 9 10 9 11 11 11 11 10 11 9 2 8 8 7 7 7 7 6 7 7 8 7 8 3 3 3 3 3 2 2 2 3 3 3 2 2 3 . . . . . 0 0 0 0 6 7 ; run;     Data want; length ScoreVector1 ScoreVector2 ScoreVector3 ScoreVector4 $32.; set tbl1; ScoreVector1=compress(Score1)||','|| compress(Score1)||','|| compress(Score3) ||','|| compress(Score4)||','|| compress(Score5)||','|| compress(Score6) ||','|| compress(Score7)||','|| compress(Score8)||','|| compress(Score9) ||','|| compress(Score10)||','|| compress(Score11)||','|| compress(Score12); Run; ... View more

Hello I want to control font size in all tables that are exported in  ODS tagsets.ExcelXP. I want to have tables in excel with excel size 8. What is the way to control data font size in excel exported output from  ODS tagsets.ExcelXP?   Thaks   ... View more

Hello In the insurance company where I work there are raw data tables that publish every month. (For  example:Raw_1711  is for NOV 2017) Every month I need to use specific raw data files(determine be manager) and perform some summary statistics on them. Then I need to create an output summary report(one table) that contain information about the requested tables. My manager ask me to automat export the summary tables +raw data tables to one excel file in multiple sheets. My task is to create a flexible program that know to export it . I have a problem with ODS tagsets.excelxp  step . Please see the tables below:     Data Raw_1711; input x y; cards; 1 10.1 2 20 3 30 ; Run; Data Raw_1712; input x y; cards; 4 40 5 50 6 60 ; Run; Data Raw_1801; input x y; cards; 7 70 8 80 9 90 ; Run; Data Raw_1802; input x y; cards; 10 100 11 110 12 120 ; Run; Data Raw_1803; input x y; cards; 13 130 14 140 15 150 ; Run; Data Raw_1804; input x y; cards; 16 160 17 170.1 18 180.2 ; Run; Data Raw_1805; input x y; cards; 19 190 20 200.3 21 210 ; Run;   %let vector=1804+1803+1712; %let k=3; %put &vector.; %macro Macro1; %DO i=1 %TO &k.; mon&i.=%scan(&vector.,&i.,+); Call Symput("mon&i.",mon&i.); %end; %mend; Data _null_; %Macro1; Run; %put &mon1.; %put &mon2.; %put &mon3.; %macro Macro2; %DO i=1 %TO &k.; %let mon=%scan(&vector.,&i.,+); Data Mydata_&mon.; SET Raw_&mon.(where=(int(y)=y)); Run; %end; %mend; %Macro2; %macro Macro3; %DO i=1 %TO &k.; %let mon=%scan(&vector.,&i.,+); PROC SQL; create table Summary&mon. as select sum(y) as Sum_Y, min(y) as Min_Y, max(y) as Max_Y from Mydata_&mon. ; QUIT; %end; %mend; %Macro3;   %macro Macro4; %DO i=1 %TO &k.; %let mon=%scan(&vector.,&i.,+); proc transpose data=Summary&mon. prefix=Y_&mon. out=Summary2_&mon.; run; %end; %mend; %Macro4;   %macro Macro5; %DO i=1 %TO &k.; %let mon=%scan(&vector.,&i.,+); Summary2_&mon. %end; %mend; Data FinalOutput; Merge %Macro5; Run;   /* My target is to use ODS tagsets.excelxp to export folloeing tables to excel*/ /*I want to have a flexible code that foe each user arguments in vector parameter we will get automatic export to excel .Each table in a separate sheeet*/ proc print data=FinalOutput noobs;Run; proc print data=Mydata_1804 noobs;Run; proc print data=Mydata_1803 noobs;Run; proc print data=Mydata_1712 noobs;Run;   ... View more

Hello Please see this program. Why  %put &Mon3.;  is  not recognized???     %let vector=1804+1803+1712; %let k=3; %put &vector.; %macro Macro0; %DO i=1 %TO &k.; %let mon&i.=%scan(&vector.,&i.,+); %end; %mend; %Macro0; %put &Mon1.; %put &Mon2.; %put &Mon3.; ... View more

Hello I have a question please. I will try to simplify it by a simple example. There is an external table that I receive. In this table for example there are 3 fields and should be for example 4 rows. For example:The data should be like that:   a   b   c   d   e   f   g   h   I   J   k   L But I get it like that:  abc  de  f  g     h     I  j      K     L I want to ask If there is a way to fix the data in a code. For example: I want to have in each row 3 fields only and each field will contain 1 words(string). Best solution will be to have a flexible program that can fix data for any number of fields. thanks Roms   ... View more

Hello I want to ask please how to create the desired output with one proc statement.  Required Output: Cat    Freq      Sum_Wellness      Avg_Wellness A       7            440                          62.8 B       3            110                          36.6 All     10            550                         55.0     Raw data : Data aaa; Input lakID Cat $ Wellness; Cards; 1 A 10 2 A 30 3 B 20 4 A 60 5 B 70 6 B 20 7 A 50 8 A 100 9 A 40 10 A 150 ; Run; ... View more

Hello I am trying to learn using str function. Please see example below. In data set test  the length of varaible  groupCat1 groupCat2 is too short. How can I add length statement to following code? data test; input age @@; &age4.; &age2.; datalines; 19 50 10 34 44 15 30 ; run;       Here is the full code:   %let age4=%str(if age<20 then groupCat1 ='<20'; else if age<35 then groupCat1='20-34'; else if age<45 then groupCat1='35-44'; else groupCat1='45+'; ); %let age2=%str(if age<35 then groupCat2='<35'; else groupCat2='35+';); data test; input age @@; &age4.; &age2.; datalines; 19 50 10 34 44 15 30 ; run; ... View more