About Steelers_In_DC

Steelers_In_DC · ‎07-10-2015

I would like to build a macro variable within a macro. The following doesn't work but I'm hoping it will give you some idea of what I'm trying to accomplish. The call symput lines are where my question is. How do I go about accomplishing this in a loop? Is it possible to change the libname and then change the data source each iteration? It's easy enough to set up 12 macro variables prior to this step but I was hoping to avoid doing that. %macro cib; %do i= &num_month %to 01 %by -1; call symputx('lastmon',substr(put(intnx('month',date9,-&i,'e'),date9.),3,3),'g'); call symputx('monyear',substr(put(intnx('month',date9,-&i,'e'),mmddyy10.),1,2)||substr(put(intnx('month',date9,-&i,'e'),date9.),6,4),'g'); libname &lastmon "/sas/data/execution/ccmr/&lastmon"; data cust_inst_base_&monyear; set &lastmon..cust_inst_base_&monyear; run; %end; ; run; %mend; %cib; Thank You in advance, Mark Go Pirates Message was edited by: Mark Johnson

Steelers_In_DC · ‎07-10-2015

You aren't losing the format in your csv, if you open the csv in excel or another spreadsheet application that is where you are losing your format. Test exporting then importing the csv, the formats will hold. The problem is with your spreadsheet. Try ods, here is a template that I use: ods tagsets.ExcelXP path='PATH' file='OUTPUT.xml' style=printer; ods tagsets.ExcelXP options(sheet_name='SHEET NAME ONE' AUTOFILTER = 'ALL'); proc print data=FILE1 noobs; var var1 var2 / style(column)={tagattr='Format:@'}; /* format as character for leading zeros */ var var3 var4 / style={tagattr='Format:#,###'};run;quit; /* format comma */ run;quit; ods tagsets.ExcelXP options(sheet_name='SHEET NAME TWO' AUTOFILTER = 'ALL'); proc print data=FILE2 noobs; var _all_;run;quit; ods tagsets.ExcelXP close;

Steelers_In_DC · ‎07-10-2015

Here's what you would want: data want; set have; count = countc(id,'-'); if count = 1 then do; New_ID = scan(id,1,'-'); end; if count ne 1 then do; New_ID = ID; end; drop count; run; The changes I made, remove the rename statement from the first line, we won't be renaming anything, and I'm only dropping the count at the bottom, I used that to identify what has more than one hyphens.

Steelers_In_DC · ‎07-09-2015

Here you go: DATA have; infile cards dsd; informat id $10.; format id $10.; input id; cards; 1170192-01 1171775-01 1175345-01 12-06-0219 12-06-0223 12-06-0235 12-06-0236 12-06-0263 13-06-0291 13-06-0294 13-06-0321 14-06-0347 14-06-0351 14-06-0597 15-06-0116 15-06-0353 15-06-0365 15-06-0367 15-06-0369 15-06-0371 99-06-9921 SP0100470- SP080004-0 SP080005-0 ; run; data want;*(rename=(new_id=id)); set have; count = countc(id,'-'); if count = 1 then do; New_ID = scan(id,1,'-'); end; if count ne 1 then do; New_ID = ID; end; /*drop id count;*/ run; After you run it and see what the dataset looks like uncomment the few things that are commented and see if that is the desired output.

Steelers_In_DC · ‎07-09-2015

You lines are out of order. Your set statement has to come before you reference any variables. It should be like this: data want; set have; Total_Sec=completed-opened; run;

Steelers_In_DC · ‎07-09-2015

I have the time so I did it but I agree with Miquel, you should definitely look up intck and intnx: DATA have; infile cards dsd; informat opened completed time.; format opened completed time.; input opened completed; cards; 07:31:00,07:46:00 07:48:00,07:52:00 07:55:00,07:57:00 08:30:00,08:33:00 10:45:00,10:52:00 21:15:00,21:28:00 19:54:00,19:57:00 09:45:00,09:48:00 10:52:00,10:56:00 11:47:00,11:52:00 ; run; data want; set have; total_sec = intck('second',opened,completed); run; Although when I run your code I get the same answer. The reason you want to use intck and intnx is if you are changing the 'second' to minute day or month. This works for me: data want; set have; Total_Sec=completed-opened; run; If it doesn't work for you you might want to check the data quality of that first line.

Steelers_In_DC · ‎07-09-2015

Here you go: DATA have; infile cards dsd; informat Item_Id $8. Category $5. Item_Description $24. Date mmddyy10.; format Item_Id $8. Category $5. Item_Description $24. Date mmddyy10.; input Item_Id$ Category$ Item_Description$ Date AGS BC MX; cards; 23555212,Chips,Potato Lays Chips 50 gms,2/1/2015,22,,31 23555212,Chips,Potato Lays Chips 50 gms,3/1/2015,,,33 23555212,Chips,Potato Lays Chips 50 gms,5/1/2015,25,,32 23555212,Chips,Potato Lays Chips 50 gms,6/1/2015,27,, 23555212,Chips,Potato Lays Chips 50 gms,7/1/2015,22,, 35442011,Chips,Chilly Lays Chips 50 gms,2/1/2015,,34, 35442011,Chips,Chilly Lays Chips 50 gms,3/1/2015,,31, 35442011,Chips,Chilly Lays Chips 50 gms,4/1/2015,,, 35442011,Chips,Chilly Lays Chips 50 gms,5/1/2015,,32, 35442011,Chips,Chilly Lays Chips 50 gms,6/1/2015,,34, ; run; proc sql; create table prep as select *,avg(ags) as _avg_ags,avg(bc)as _avg_bc,avg(mx) as _avg_mx from have group by item_id order by item_id,date; data want; set prep; by item_id; if missing(ags) then ags = _avg_ags; if missing(bc) then bc = _avg_bc; if missing(mx) then mx = _avg_mx; drop _:; run;

Steelers_In_DC · ‎07-09-2015

I'm not able to open the attachments, if you can copy/paste or type something in I'll help further.

Steelers_In_DC · ‎07-08-2015

You want to use a by statement to fill in the blanks: proc expand data=have out=want from=minute to=minute observed=end method=none; by id; id minute; run; You can write out the log to anywhere you want but I never recommend doing this. It would go like this: proc printto log="/sas/user_data/local/filename.log"; run; proc expand data=have out=want from=minute to=minute observed=end method=none; by id; id minute; run; proc printto;run; Anytime I've used this the log was unusable, I would advise you to find out why you are getting the warnings and try to make an adjustment. If the warnings make the other part of the log unusable then I might do this but I only use it as a last resort.

Steelers_In_DC · ‎07-08-2015

Your want table doesn't come from the have table but I think I have a solution for you. data have; infile cards dsd; informat Mem_ID $3. start_date end_date yymmn6.; format Mem_ID $3. start_date end_date yymmn6.; input Mem_ID$ start_date end_date; cards; 123,201401,201412 234,201208,201501 345,201211,201403 456,201306,201412 567,201401,201406 ; proc sql; create table want as select start_date as Year_Month,count(mem_id) as Count_of_members from have group by start_date order by start_date;

Steelers_In_DC · ‎07-08-2015

If this isn't helpful provide an example, two datasets, of what you have and what you want.

Steelers_In_DC · ‎07-08-2015

Here you go, let me know if I missed a step: data have; infile cards dsd; informat ID $1. begin_date end_date date1 dot1 mmddyy8.; format ID $1. begin_date end_date date1 dot1 mmddyy8.; input ID begin_date end_date date1 dot1; cards; 1,02/12/06,04/29/06,02/12/06,04/29/16 1,03/14/07,01/15/08,03/14/07,04/18/07 1,03/14/07,01/15/08,04/18/07,05/16/07 1,03/14/07,01/15/08,05/16/07,09/02/07 1,03/14/07,01/15/08,09/02/07,01/15/08 1,05/15/10,08/05/12,06/26/10,08/15/10 1,05/15/10,08/05/12,01/20/11,01/25/11 1,05/15/10,08/05/12,02/01/11,08/01/11 1,05/15/10,08/05/12,04/01/12,04/03/12 ; data want; set have; by id begin_date; if first.begin_date then trip + 1; run;

Steelers_In_DC · ‎07-08-2015

You can use proc format, or proc sql; proc sql noprint; select 'variable' into :prefixlist separated by ',' from table; This will give you a macro variable &prefixlist

Steelers_In_DC · ‎07-08-2015

I'd add a step to identify missing. Not sure that I would have come up with the same solution from the start but after looking at the transpose that makes sense to keep. Although I enjoyed the smiley faces that didn't really answer my previous question. Below I am loading the file as CSV, so the blank comes through and is clearly missing due to the comma. This will work, not sure if it suits your purposes. data master; infile datalines dsd; input ID $ var1 $ var2 $; datalines; 123,Yes,Red 124,No,Blue 125,Yes,Yellow ; data correct; infile datalines dsd; input ID $ variable $ correct $; datalines; 123,var1,No 123,var2,Orange 124,var2,Red 125,var1, ; data correct_II; set correct; if missing(correct) then correct = 'missing'; run; proc sort data=Correct;by ID;run; proc transpose data=correct_II out=cwide (drop=_name_); by ID; var correct; id variable; run; data final; update master cwide; by id; if var1 = 'missing' then call missing(var1); if var2 = 'missing' then call missing(var2); run;

Steelers_In_DC · ‎07-08-2015

Here's a start: data dateat; infile cards; informat ln_no $10. TPR_DATA_AS_OF_DT date9. Month YEARMONTH_KEY yymmn6.; format ln_no $10. TPR_DATA_AS_OF_DT date9. Month YEARMONTH_KEY yymmn6.; input LN_NO$ TPR_DATA_AS_OF_DT Month YEARMONTH_KEY date1; cards; 0000000000 31Dec2014 201412 201412 201412 0000000000 29Nov2014 201411 201411 201411 0000000000 31Oct2014 201410 201410 201410 0000000000 30Sep2014 201409 201409 201409 0000000000 30Aug2014 201408 201408 201408 0000000000 31Jul2014 201407 201407 201407 ; run; %macro date1; %do i = 201412 %to 201407 %by -1; data d_monthly_&i; set dateat; where date1 = &i; run; %end; %mend; %date1;

Online Status	Offline
Date Last Visited	‎07-12-2025 12:12 PM

sql server using 'with'

Re: hive show table and column

hive show table and column

proc sql using or

find edge node

Re: Fill in rows with previous value if next value is the same.

Re: Fill in rows with previous value if next value is the same.

Re: Fill in rows with previous value if next value is the same.

Fill in rows with previous value if next value is the same.

Re: reg ex extract

Re: proc sql using or

Re: Adding quotes to a Macro Variable

Re: How to run a program in EG5.1 without connecting to a server

Re: macro variable in set statements based on today()

Finding all single linked chains: the allChains macro

find edge node

infile 'red x' with no error in log

Re: Extracting Month, Year and quarter

Re: Which Date function can be used to extract month(Character) from t...

Re: How to assign a column value based on another column ??

macro variable in macro

Re: loss of formatted values in the exported CSV file

Re: Extract new ID from records with one hyphen

Re: Extract new ID from records with one hyphen

Re: Subtracting time

Re: Subtracting time

Re: how to consider Average Value for missing Prices?

Re: Data step: Anyway to combine multiple observations into one?

Re: Fill in missing minutes to a dataset

Re: Calculating members

Re: How to compare a value with a list stored in another file?

Re: Create visit variable

Re: How to compare a value with a list stored in another file?

Re: Data cleaning after double-entry with missing

Re: Using date macro to segment into separate datasets