About RW9

RW9 · ‎07-07-2014

Why do you need to use proc tabulate for that? proc sql; create table WANT as select distinct A.CLASS, A.FEMALE_COUNT, B.SUM_OF_MARKS from ( select CLASS, COUNT(*) as FEMALE_COUNT from HAVE having GENDER="F") A left join (select CLASS, SUM(MARK) as as SUM_OF_MARKS from HAVE); on A.CLASS=B.CLASS; quit;

RW9 · ‎07-07-2014

Yes, if you do the following you will see the grouping. data have; attrib a b c d e format=best.; do I=1 to 10; do j=1 to 5; do k=1 to 7; do l=1 to 6; do m=1 to 3; a=i; b=j; c=k; d=l; e=1; output; end; end; end; end; end; run; data want; set have; by a b c d e; if first.e or last.e then Tick="Y"; run;

RW9 · ‎07-07-2014

Change it to; if first.month then cumvalue= value; This will do each subgroup within the first group.

RW9 · ‎07-07-2014

How are you using the macro variable? It depends on the situation, but you could do a check in your macro per: %MACRO MACRO1(TPM,X1); %if %sysfunc(anyalpha("&TPM."))>0 %then %put "Non numeric found"; %else %PUT "This is the value of &TPM"; %MEND MACRO1; %MACRO1(TPM=a10a00,X1=TEST);

RW9 · ‎07-07-2014

To be facetious, at the beginning. There are SAS books available via many book outlets or SAS themselves. SAS provides certification courses. You will need licensed software, so if you are at college then they should have the training edition. If you are at work then they would have it. It depends very much on your situation, do you have access to the software. , have you programmed before, what do you intend using the software for as there are many components, will you be stats orientated etc. I would start by having a look at SAS main page at the various components, and what you would be using it for, then look at SAS Base which is core. Not sure if SAS do a learning edition any more other than the college one.

RW9 · ‎07-07-2014

I suspect he is asking about the functionality in Excel to attach a comment to a cell. I don;t believe this is directly possible through SAS export, however if you wish to put the time into learning Open Office and compile the files yourself you could do it. For the second part, perhaps putting comments on a separate page, then this is possible. You can use either proc export multiple times to write multiple sheets, or my suggestion would be to use ods tagset.excelxp (search for this, there are many topics on it). Put your data in one dataset, comments in another, then proc report each of them with ods statements to get a multi-tab spreadsheet with data on one sheet and comments on another.

RW9 · ‎07-04-2014

This works for me: data have; attrib date format=date9.; date='05JAN2014'd; output; date='31JAN2014'd; output; run; data want; set have; if date ne intnx('month',date,0,'e') then delete; run;

RW9 · ‎07-04-2014

Try the other example given: data want; set have; if date ne intnx(‘month’,date,0,’e’) then delete; run; This will give you the last day in the month

RW9 · ‎07-04-2014

Maybe have a look at intnx: http://www.datimpact.be/tips/finding-the-last-day-of-the-month-in-sas-using-intnx.php Or: https://philihp.com/blog/2011/how-to-get-the-last-day-of-a-month-in-sas/

RW9 · ‎07-04-2014

To note, there is a very similar thread here: https://communities.sas.com/message/218426#218426

RW9 · ‎07-04-2014

Hi, Why would you want to use arrays? I have seen that people use them when going from normalized data to transposed data to have more control over the output, but arrays don't make much sense when going from transposed to normalized data. If you really want to do it another way then the below code is a suggestion: proc sql; create table WORK.LOOP as select distinct NAME,TYPE from SASHELP.VCOLUMN where LIBNAME="SASHELP" and MEMNAME="CLASS" and NAME ne "Name"; quit; data _null_; set work.loop end=last; if _n_=1 then call execute('data want (keep=name section result); set sashelp.class; attrib section result format=$200.;'); if type="num" then call execute(' section="'||strip(name)||'"; result=strip(put('||strip(name)||',best.)); output;'); if type="char" then call execute(' section="'||strip(name)||'"; result='||strip(name)||'; output;'); if last then call execute('run;'); run;

RW9 · ‎07-04-2014

To note, the excelxp tagset creates XML output, which by its nature is very verbose. I would firstly ask why you would want 20k records in Excel, which is not a database tool, as its impractical to actually do anything with the data in Excel. If you want reports, then generate the output from SAS. If you really must output large quatities to Excel, then using CSV delimited output would result in probably the smallest file size. Perhaps an explanation of what you are trying to achieve?

RW9 · ‎07-04-2014

That's not transposing as such, but normalizing. To transpose you take vertical data and make it horizontal, and for normalizing its the reverse. Something like: data want (keep=section center male female); set have; attrib section format=$200. male female format=best.; section="Blood Level"; male=blood_level_male; female=blood_level_female; output; section="BMI"; male=bmi_male; female=bmi_female; output; run;

RW9 · ‎07-04-2014

Sorry, I don't see why you would want to do this. It seems very random to just merge data to other data based on position. The reason you have the data merging to all the other data is that you by group, or matching variables are not enough to show a distinct merge. If for instance I sort the input slightly differently I would get different results, hence not a good idea. Personally I would investigate what actually needs to be merged together and use other variables, or assign variables to make that merge explicit. If you really only want position in the dataset (which as I mentioned could be different each time), then assign an order variable and merge on that as well, e.g. DATA_A : BOAVTS01 E082 E08 1 BOAVTS01 E242 E24 2 BOAVTS01 E282 E28 3 BOAVTS01 4102 F20 4 BOAVTS01 E522 F52 5 BOAVTS01 E102 G10 6 BOAVTS01 E302 G30 7 TXSVTS30 3302 1G 1 TXSVTS30 3002 3S 1 DATA_B; BOAVTS01 UKDUKC01 1 BOAVTS01 UKDUKC02 2 BOAVTS01 UKDUKC03 3 BOAVTS01 UKDUKC04 4 TXSVTS30 KCDTXS36 1 TXSVTS30 KCDTXS38 2

RW9 · ‎07-04-2014

Sorry, am a code monkey not a stato, its all greek to me. Maybe post a question in the stats section.

Re: How to import excel with guessingrow function

Re: How to retain the Variable Format when PROC EXPORT TO EXCEL

Re: Deleting rows in Excel with SAS

Re: PROC SGPLOT one command to produce graph for each case? And, quest...

Re: Deleting rows in Excel with SAS

Re: put statement and line break using /

Re: SAS code

Re: How to read from pdf to SAS

Re: Removing last word with length 1 or 2

Re: How to read from pdf to SAS

Re: How to read a macro variable value and convert it in SAS data set

Re: proc format in permanent library

Re: Copy all tables beginning with certain keyword from one lib to oth...

Re: Automated jobs failing because SAS logs contains Warning messages.

Re: Error: BY variables are not properly sorted on data set

Re: Difference between itt and safety populations

Re: ods excel -multiple sheets

Re: Lag function help

Re: ERROR: User does not have appropriate authorization level for file...

Re: Multiple graph in one page

Maxims of Maximally Efficient SAS Programmers

Re: how can I use proc tabulate to display only some of the responses ...

Re: Cumulative Sum by 2 Groups

Re: Cumulative Sum by 2 Groups

Re: passing numeric value in the MACRO

Re: Am new in SAS..How can i start with SAS Programming?

Re: How to add note to Excel when export SAS data?

Re: Select end month dates from daily data for each company

Re: Select end month dates from daily data for each company

Re: Select end month dates from daily data for each company

Re: Transpose multiple variables

Re: Transpose using array

Re: Exporting datasets takes long time

Re: Transpose multiple variables

Re: How to create Match merge with n x n observations and Missing ?

Re: Iterative sum with factorial factors

SAS Global Forum 2018