About commitsudoku

commitsudoku · ‎08-26-2022

Thank you so much; I had put the ampersand (it was a mistake in my post but not in the code) but I didn't know I had to put two dots before the "xlsx". It works like a charm now. Thank you very much.

commitsudoku · ‎08-26-2022

Yes, I had put the ampersand before the i; I just forgot it in my post. This is exactly what I needed though ; "tabulate_&i..xlsx". I just didn't know I had to put two dots. It now works like a charm, thank you ! As for the "it didn't work", like I said ; SAS generated 3 corrupt files that I couldn't open with any software.

commitsudoku · ‎08-26-2022

Hi, I am trying to generate an Excel report for each month of a given year. So, the following macro generates a new variable, MMOIS to chose a month and uses that variable to generate a PROC TABULATE for that month; so in this simplified example, a report is created for the first three monthes of the year (JAN, FEB and MAR). %macro comp; %local i ; /*For JAN, FEB and MAR*/ %do i=1 %to 3; data temp; set temp ; IF (MONTH = &i AND ANNEE=&this_year) THEN MMOIS='Curr' ; IF (MONTH = &i AND ANNEE=&last_year) THEN MMOIS='Prev' ; run; ods excel file="\\DIRECTORY\tabulate_i.xlsx" options (sheet_interval='PROC'); PROC TABULATE DATA = temp out=secteurs_apen; CLASS region mmois / MLF ; TABLE (region=''), (mmois='') ; run; data temp; set temp ; DROP MMOIS; run; ods excel close; %end; %mend comp; %comp The problem arises in the following part : ods excel file="\\DIRECTORY\tabulate_i.xlsx" This command generates 3 corrupt files names tabulate_1xlsx, tabulate_2xlsx and tabulate_3xlsx. But what I really want is to have 3 Excel files like "tabulate_1.xlsx". I have even tried to generate using CAT as in : ods excel file=cat("\\DIRECTORY\tabulate",&i,".xlsx") ...but it didn't work. So, is there a way to generate an XLSX file for each iteration using ODS EXCEL ? How can I add ".xlsx" after the "&i"? Thank you very much,

commitsudoku · ‎10-13-2021

Perfect, thank you very much! It's a longer solution than what I expected but this is exactly what I wanted. Thank you very much!

commitsudoku · ‎10-01-2021

Well it's more or less the idea, but still I don't get a result for the Chi-squre, because the PROC FREQ doesn't put an Origin vs all the others. With the BY statement, I only get a table for that Origin and the Khi-square test cannot the applied.

commitsudoku · ‎10-01-2021

Hello, For instance, in SASHELP.CARS, if I want to see if the Origin and the Type of the cars are independent, I can test for independence between these two variables, using : PROC FREQ data=sashelp.cars; tables (origin)*type / chisq ; run; Then, I get a Chi-square test value at 35,66 and a p-value<0.0001 ; so, I can reject the null hypotesis for the globality of the frequency table. But, what happens if I want to do this for each item of Origin; not only to see if there is a broad dependence, but to test the independence for Asia, then Europe, then USA and so on ? Is there a better way that making the following code for each region ? PROC FREQ data=sashelp.cars; where origin in ('Asia', 'Europe'); tables (origin)*type / chisq ; run; Is there an option to have the Chi-square test for each item of the PROF FREQ, not only for the whole? Thanks!

commitsudoku · ‎07-06-2021

Thank you so much! It worked perfectly!

commitsudoku · ‎07-02-2021

Hi. I have a CSV file with 5600 rows and 331 columns. I successfully import the data using PROC IMPORT into RESULTS. Once imported I can visualize the data in SAS and even use PROC FREQ and PROC TABULATE on RESULTS. But I need to recode some variables and for that I need to use PROC DATA. So a very simple command just to put my imported data into a DATA PROC. DATA data; SET RESULTS; RUN; But this gives the following error: ERROR: The decimal specification of 15 must be less than the width specification of 15 Why is that? Thank you,

commitsudoku · ‎08-26-2020

Thanks to all the bits you gave, I think I have found the solution, without using the BY statemen. So here is the code that worked for me. I thank you all very much for your help! data data; set sashelp.cars;run; data classes; input id_reg reg_name $ ; cards; 1 Asia 2 Europe 3 USA run; %macro comp; %local i ; %do i=1 %to 3; proc sql ; select reg_name into :reg_name from classes where id_reg = &i.; quit; PROC TABULATE DATA = data(where=(Origin="&reg_name")) out=tabulate_&i; CLASS Make DriveTrain ; TABLE (Make), (DriveTrain) / nocellmerge ; run; %end; %mend comp; %comp

commitsudoku · ‎08-26-2020

Sorry, but I don't understand except putting "&". So, here is my code, with your suggestions. But it still does not work: %macro comp; %local i reg; %do i=1 %to 3; proc sql ; select reg_name into :reg_name from classes where id_reg = &i.; quit; %let reg=input(reg_name,$10); PROC TABULATE DATA = data out=tabulate_&i; where Origin in (&reg); CLASS Make DriveTrain ; TABLE (Make), (DriveTrain) / nocellmerge ; run; %end; %mend comp; %comp

commitsudoku · ‎08-26-2020

Yes, I understand but I made a simple example. In the real database I work with, I need to make a macro 🙂 this is why I insist on making a macro.

commitsudoku · ‎08-26-2020

Hello, In SASHELP.CARS, I would like to make a PROC TABULATE by Origin. So, the first way is to make 3 PROC TABULATE like the following and replace "Asia" by USA and then Europe : PROC TABULATE DATA = data out=tabulate; where Origin="Asia"; CLASS Make DriveTrain ; TABLE (Make), (DriveTrain) / nocellmerge ; run; But, instead, I would like to automatize this in a macro loop. Could you please help me why the following code won’t work ? It’s the « where Origin=reg; » part that seems to be the problem. Thank you ! data data; set sashelp.cars;run; data classes; input id_reg reg_name $ ; cards; 1 Asia 2 Europe 3 USA run; %macro comp; %local i reg; %do i=1 %to 3; proc sql ; select reg_name into :reg_name from classes where id_reg = &i.; quit; %let reg=reg_name; PROC TABULATE DATA = data out=tabulate_&i; where Origin=reg; CLASS Make DriveTrain ; TABLE (Make), (DriveTrain) / nocellmerge ; run; %end; %mend comp; %comp

Online Status	Offline
Date Last Visited	‎08-30-2022 02:33 PM

Re: Generate a new XLSX file for each iteration in a macro with ODS EX...

Re: Generate a new XLSX file for each iteration in a macro with ODS EX...

Generate a new XLSX file for each iteration in a macro with ODS EXCEL

Re: How to make a Chi-square independence test for each item of the fr...

Re: How to make a Chi-square independence test for each item of the fr...

How to make a Chi-square independence test for each item of the freque...

Re: ERROR: The decimal specification of 15 must be less than the width...

ERROR: The decimal specification of 15 must be less than the width spe...

Re: Macro loop to generate Proc Tabulate by origin

Re: Macro loop to generate Proc Tabulate by origin

Re: Generate a new XLSX file for each iteration in a macro with ODS EX...

Re: Generate a new XLSX file for each iteration in a macro with ODS EX...

Re: How to make a Chi-square independence test for each item of the fr...

Re: ERROR: The decimal specification of 15 must be less than the width...

Re: ERROR: The decimal specification of 15 must be less than the width...

Re: Generate a new XLSX file for each iteration in a macro with ODS EX...

Re: Generate a new XLSX file for each iteration in a macro with ODS EX...

Generate a new XLSX file for each iteration in a macro with ODS EXCEL

Re: How to make a Chi-square independence test for each item of the fr...

Re: How to make a Chi-square independence test for each item of the fr...

How to make a Chi-square independence test for each item of the freque...

Re: ERROR: The decimal specification of 15 must be less than the width...

ERROR: The decimal specification of 15 must be less than the width spe...

Re: Macro loop to generate Proc Tabulate by origin

Re: Macro loop to generate Proc Tabulate by origin

Re: Macro loop to generate Proc Tabulate by origin

Macro loop to generate Proc Tabulate by origin