About jiangmi

jiangmi · ‎03-17-2015

There is no formula for the relationship. Different variables (var_ ) are picked for different runs to feed the V0-V7, which were used in a following macro. What I can do it to put them together in a data set like this: data have; input target$ feed$; cards; V0 var_4 V1 var_5 V2 var_6 V3 var_8 V4 var_9 V5 var_10 V6 var_13 V7 var_15 ;

jiangmi · ‎03-17-2015

Hi, All, I ran into a situation that I have to assign multiple values to multiple macro variables like the following: %let V0 = var_4; %let V1 = var_5; %let V2 = var_6; %let V3 = var_8; %let V4 = var_9; %let V5 = var_10; %let V6 = var_13; %let V7 = var_15; My question is: Is there anyway we can simplify this process to pair up macro values to macro variables, instead of writing 8 %let statements? Thanks. Joe

jiangmi · ‎02-13-2015

I didn't realized that modification in sql. Thanks. Joe

jiangmi · ‎02-13-2015

Hi, All, In this case, &num was created by SQL to count the number of obs in data(have). Instead using &num directly to create array, I have to assign the &num value to &end to make it work. Why? The log screen was attached. Thanks. Joe data have; input year_mth @@; cards; 201412 201411 201410 ;run; proc sql; select count (distinct year_mth) into:num from have ;quit; %put check the new macro value: &num; /*Why not working if use &num directly*/ data notwork; array L (*) L01-L&num; do i=01 to &num; L(i)=i; end; run; /*It works when converting by let statement*/ %let end = &num; data yeswork; array L (*) L01-L&end; do i=01 to &end; L(i)=i; end; run;

jiangmi · ‎02-13-2015

Thanks, Reeza, I actually read your old post on the same issue: I am running SAS EG on server, I don't have install privilege either. The ' DBMS=ExcelCS' doesn't seem to work. Putting macros in ods tagsets isn't promising either, after copying pasting the code and several modification tries. It may has something to do with my connection to the server, cause I got error msg like 'ERROR: Server Name is invalid or missing.'. I will surely try them on my local SAS later today. Thanks again.

jiangmi · ‎02-12-2015

What kind of hotfix?

jiangmi · ‎02-12-2015

Hi, All, Is there a way that we can append results from macro runs to multiple tabs in one single excel file? Take an example like below, each time the macro run, new output will replace the old. However, I want both results to be present in one excel file (picture show). I also tried ODS TAGSETS, it doesn't seem to work either. Please offer opinions. Thanks. Joe data have1; sex='M';run; data have2; sex='F';run; %macro test (data, var); proc export data=&data (where=(sex="&var")) outfile="/SASData/SASUSER/Range/why2.xlsx" dbms=xlsx replace; sheet="&var"; run; %mend test; %test(have1, M) %test(have2, F)

jiangmi · ‎02-09-2015

In my case, if there are missing value, denominator will be 2 or less. I worry more about putting the year_mth in the wrong group. e.g. G3 will always include 201410, 201409, and 201408. Although 201408 is missing in this case, I don't want to see 201407 to be put in G3. Every month you pull the new data out, the newest time period will always be on the top (201501 will be on the top if I pull the data today). The new G1 should be 201501, 201412, and 201411. The new G3 will be 201409, 201408, and 201407. I am sorry if I still can not make my self understood or confuse you. Thanks for your time. Joe

jiangmi · ‎02-09-2015

Thank, Ballardw. I understand how lag function works and actually used it in one of my project to create lagged sampling windows. This question here came from that same project. Right now, the data I have is perfect, without any broken time period/missing period. Therefore, my code works fine. However, I did see some times there will be missing time periods in certain data. And if that happens, my code won't work. Thus, I am trying to make my code robust (or ready) to this kind of situations. After observations are assigned to certain groups (G1-G5), an average of 'value' for each group will be calculated. An averges trend will be plotted after that. Notice that G3, G4, and G5 will only have two obs due to missing data. I hope this make sense. Thanks again. Joe

jiangmi · ‎02-09-2015

Hi, All I just came up with this question while working on my stuffs. I am creating a 3-month sampling window with one month lag to calculate the mean of the value. I have 'year_mth' and 'value' in my data set. If I have 201408 data, then I can do a lag and assign the sampling window correctly. However, in the case when 201408 data is missing, I assume lag function won't work. I think I will have to do the window assignment based on actual month and year, which caused me big headache. Anybody had similar issue before or good idea? Thanks. Joe data have; input year_mth value; cards; 201412 77 201411 65 201410 53 201409 29 201407 58 201406 62 ;run;

jiangmi · ‎02-07-2015

Thanks PG for your extra help. I see your point. I may use it later. Thanks. Joe

jiangmi · ‎02-06-2015

It is still helpful. Thanks.

jiangmi · ‎02-06-2015

Hi, Reeza, What do you mean by saying 'usually for rolling calculations you don't need to go into macros'? Do you have an example? I'd like to see if it can simplify my process. In my case, I need to create the rolling windows and then do certain calculation for each window using PROC NPAR1WAY. Thanks. Joe

jiangmi · ‎02-06-2015

Hi, All, I am creating some 2-month rolling sampling windows with array. Right now,the result data set (want) has variables G1-G12 created from array. Is there anyway I can have G01, G02, ... instead of G1, G2,...? Thanks. Joe /**/ data have; input year_mth @@; cards; 201412 201411 201410 201409 201408 201407 201406 201405 201404 201403 201402 201401 201312 201311 201310 201309 201308 201307 201306 201305 201304 201303 201302 201301 201212 201211 201210 201209 201208 201207 201206 201205 201204 201203 201202 ; run; %macro new; proc sql; select count (distinct year_mth) into:numofbatch from have ;quit; *determine how many lagged group I can have; %let end=%sysfunc(floor ((&numofbatch-12)/2)); %let end2=%eval(&end+1); *Create rolling sample windows; data want; set have; G1= (_n_ <=12); array G (&end2); do n=1 to &end; G(n+1)= lag2(G(n)); end; run; %mend; %new

jiangmi · ‎01-22-2015

Thank you all. I will study your comments and give feed back later. Joe

Online Status	Offline
Date Last Visited	‎05-12-2020 09:11 PM

Re: IF THEN Confusion.

Re: IF THEN Confusion.

IF THEN Confusion.

Re: Measure the max length of decimal places

Measure the max length of decimal places

Re: New variable assignment not working?

New variable assignment not working?

Re: Date Variable to be put into macro in %let.

Re: Date Variable to be put into macro in %let.

Date Variable to be put into macro in %let.

Re: Measure the max length of decimal places

Re: process 'if statement' more efficiently.

Re: Simplify multiple %let statement

Simplify multiple %let statement

Re: Why macro var created from SQL can't be used immediately in array?

Why macro var created from SQL can't be used immediately in array?

Re: Macro output multiple data to multiple tabs into one excel.

Re: Macro output multiple data to multiple tabs into one excel.

Macro output multiple data to multiple tabs into one excel.

Re: Rolling sample window with broken time period.

Re: Rolling sample window with broken time period.

Rolling sample window with broken time period.

Re: Array variable format (want Var_01 instead of Var_1)

Re: Array variable format (want Var_01 instead of Var_1)

Re: Array variable format (want Var_01 instead of Var_1)

Array variable format (want Var_01 instead of Var_1)

Re: Difference between IF and WHERE statements