About forumsguy

forumsguy · ‎06-05-2013

Agree Linus, but that's what my client's requirement is. They are insisting on data steps as they previously had experienced that having mixed macro variables (few from datastep and few from proc sql) tend to create problems in their environment. Anyways, can you please elaborate on your approach (EOF and retain). I tried using that as well , however it could not work.

forumsguy · ‎06-05-2013

Hi All, Following is my dataset data have; input var1 $20; cards; robert is my boss ; run; Now , i want to have one single macro variable which would resolve to robert is my boss In short, I want to concatenate all obseervations in one single macro. Catch is not to use Proc sql. I know below single statement would have solved my purpose, but I have to use datastep and not proc sql. proc sql; select var1 into :macvar1 separated by " " from have; Any help would be highly appreciated

forumsguy · ‎05-30-2013

Hi All, My requirement is to create new dataset based on two macro variables. There are two macro variables present one to have variable list and another to have format of the variables.. So Code : %PUT OUTPUT_VARIABLE_NAME IS &OUTPUT_VARIABLE; Log : OUTPUT_VARIABLE_NAME IS ACCOUNT_ID DEFAULT_MONTH OUTCOME OUTCOME_MONTH DEFAULT_CURRENT_BALANCE OUTCOME_CURRENT_BALANCE Code : %PUT OUTPUT_VARIABLE_FORMAT is &OUTPUT_VARIABLE_FORMAT; OUTPUT_VARIABLE_FORMAT IS $20. $6. $20. $6. best12. best12. So my requirement is something like this DATA NEW(Keep=&OUTPUT_VARIABLE_NAME); FORMAT &OUTPUT_VARIABLE_NAME &OUTPUT_VARIABLE_FORMAT); RUN; Please suggest any solution.

forumsguy · ‎05-30-2013

I got it corrected. Solution is as below : if at all anyone is interested. Use compress to remove extra lines. DATA NEW; SET NEW; MACRO_PARAMETERS = COMPRESS(MACRO_PARAMETERS,'','KW'); RUN; @MODS : Please close the thread as answered

forumsguy · ‎05-29-2013

Hi all, i am reading values from excel sheet. Which has 2-3 merged cells, so when I amusing proc import I get 2-3 new lines in my dataset. I want to get rid of that since I want to create macro variable in next step of this value. Dataset THe code that I am using to create new macro variable is as below: %let TYPE_CODE=%sysfunc(getvarc(&tab_name, %sysfunc(varnum(&tab_name,TYPE_CODE)))); Now, values of TYPE_CODE is original table are as follows : CODE=1, TYPE_CODE="HL" "PL" "CC", TYPE_CODE=_ALL_ Please note that there are 3 lines above CODE=1 and this itself is one single observation in my dataset. Any help is really appreciated

forumsguy · ‎05-28-2013

Hello, My requirement is as follows: will be given a number of SAS tables with account level records. so we can join the tables based on account_id each table will contain certain attributes of an account for say 10 years but there will be one record per account. so outs_bal for an account will be in outs_bal_1 .... outs_bal_n columns Need to create a master control table containing, for example, portfolio name, macro name, location of the macro source code, and things like that using that control table, your program will read in the macro definition and run the code against those "array" datasets and append to a big monthly dataset that monthly dataset will contain account level records from the array tables pluss addtional columns created by macros already present. Anyone worked on similar things. I think of using %include in my program. Ultimately, this has to be deployed on SAS DI. So any other approach ?? Any help is appreciated

forumsguy · ‎09-12-2012

Hi there, I have special characters in my data .. My logic is whenever there is special character insert data in my error table ...Please find code below DATA TEST; INPUT VAL $256. Rec_nbr key ; DATALINES; AGReins|Tempète - 2eme tranche 50 XS 80|Broker 1 1 . AGReins|Engineering (XoL BDM et TRC) - 2ème Tranche 5 Xs 10|Broker 1 2 . RUN; %MACRO INSERT(PARAM1,PARAM2); CREATE TABLE ERROR_TABLE (DT NUMERIC FORMAT=DATETIME 20.,VALUE VARCHAR(256),REC_NBR NUMERIC); PROC SQL; INSERT INTO ERROR_TABLE VALUES (,%SYSFUNC(DATETIME()),"&PARAM2.",&PARAM1.); QUIT; %MEND; %MACRO TEST; DATA _NULL_; length val $256; SET TEST; VAL1=COMPRESS(VAL); IF Rec_nbr=. THEN CALL EXECUTE('%INSERT('||REC_NBR||', '||VAL1||')'); RUN; %END; %MEND; %TEST;

forumsguy · ‎08-02-2012

Hi Guys, I am trying to create the macro variables using a macro to use it in my code further but it seems those r restricted only to that macro... Can somebody help me with this %MACRO M1(COL,TAB,MACROVAR); PROC SQL NOPRINT; CREATE &COL INTO :MACROVAR FROM &TAB; QUIT; %MEND; %M1(MSGID,SASHELP.ADOMSG,A1); %PUT &A1; This gives me error ... is there any alternative to this ????

forumsguy · ‎05-23-2012

Hi there, I have a cumulative report to produce with ODS and output should be in PDF. Template is as attached. Need suggestions on achieving the same . Here, I have dimension name and Year of reporting(Proc label in ODS) . PDF pages should be based on dimension names.. So in Dimension table if I have 3 entries there should b 3 pages of report....Any help is really appreciated

forumsguy · ‎04-21-2012

My Concern is not the calculation. My data has all the calculations required. If you look at the output, you will find out that written and premiums have last section as Net and all premiums have last section as Total Net and just above first expense there is heading as Expenses. I am worried about only these two parts ...Thanks for all the suggestions

forumsguy · ‎04-20-2012

Hi there, I want to prepare report like insurance (1) insurance (2) insurance (3) insurance (4) written Gross A1 A2 A3 A4 accepted B1 B2 … share C1 Net D1=A1+B1-C1 Premiums Gross A1A accepted B1A share C1A Net D1A=A1A+B1A-C1A Claims Gross E1 accepted F1 share G1 Net H1=E1+F1-G1 Expenses Recycle Gross E1A accepted F1A share G1A Total Net H1A=E1A+F1A-G1A Investment Gross E1B accepted F1B share G1B Total Net H1B=E1B+F1B-G1B I have used following proc tabulate code to produce the output.. But my concern is about header Expenses and last entry for expenses subgroup(Total Net). PROC TABULATE DATA =Temp FORMAT=commax17.2 STYLE={font=("Helvetica median",8pt,Normal)}; CLASS ClassrankTP ClassrankNM ClassrankCAT ClassrankME / MISSING S=[background=&bgcol font=("Helvetica median",8pt,Normal)]; CLASSLEVEL ClassrankTP ClassrankNM ClassrankCAT ClassrankME/ S=[background=&bgcol font=("Helvetica median",8pt,Bold)]; VAR cvg_1 / S=[font=("Helvetica median",8pt,Normal)]; FORMAT ClassrankTPclassNLTPCode. ClassrankME classNLMeasureCode.ClassrankNM classNLCoverCode. ClassrankCATclassCategoryCode.; TABLES ClassrankCAT = {LABEL=' ' S=[CellWidth=150 foreground=black font=("Helvetica median",8pt,bold)]} * ClassrankME=' ', ClassrankTP=' ' * ClassrankNM=' '* cvg_1=' ' * sum=''/ BOX={LABEL='' S=[ background=black]}; RUN; Any Help would be really appreciated

forumsguy · ‎04-10-2012

Hi All, Please find the code below : I want to create variables D2, D3, D4, D5,D6,D7 and D8 from variable TPjNL based on conditions... But somehow I am not able to produce it correctly... Need help on this PROC SQL NOPRINT; CREATE TABLE TEMP AS SELECT CASE TPjNL WHEN NM="Medical " THEN D2 , ELSE WHEN NM="Income " THEN D3 , ELSE WHEN NM="Workers'" THEN D4 , ELSE WHEN NM="Motor " THEN D5 , ELSE WHEN NM="Other motor " THEN D6 , ELSE WHEN NM="Marine" THEN D7 , ELSE WHEN NM="General " THEN D8 ELSE "." END; QUIT;

forumsguy · ‎03-31-2012

Hi there, My task is to create individual datasets from the variable of my dimension. However I am facing issue with values which has single quotes... Please find the code below Data Dimension; input Name $40.; datalines; property worker's compensation calamity Life's data ; run; Proc sql; select distinct Name into : nm separated by '*' from dimension; select count distinct(Name) into :count from dimension; QUIT; %Macro m1; %do i=1 %to count1; %let nm1=%scan(%superquote(nm),&i,'*'); data new&i; set Fact; where Fact_name="&nm1"; run; %end; %mend; %m1; Here my nm1 resolves for property but not for worker's compensation... has anybody encountered any problem like this before... any help is really appreciated...

forumsguy · ‎03-30-2012

Hi Ksharp Following is my error log PROC SQL NOPRINT; 16 SELECT * FROM FACT WHERE 17 FACT.INCEP_DT BETWEEN (&NEXT_REP_PERIOD_START 18 AND &NEXT_REP_PERIOD_END); NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. NOTE: Line generated by the macro variable "NEXT_REP_PERIOD_START". 18 01JAN2012:00:00:00 _______ 22 76 ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, ), *, **, +, -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN, CONTAINS, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=. ERROR 76-322: Syntax error, statement will be ignored.

forumsguy · ‎03-30-2012

Hi All, I am facing problem in resolving date variable for given calculation; please find code below. But it is giving me error. Any help is really appreciated.... PROC SQL NOPRINT; SELECT * FROM FACT WHERE FACT.INCEP_DT BETWEEN (&NEXT_REP_PERIOD_START AND &NEXT_REP_PERIOD_END); QUIT; PROC SQL; SELECT MIN(STRT_DT) FORMAT=DATETIME20. INTO :NEXT_REP_PERIOD_START FROM PRD WHERE YEARR = 2012; SELECT MIN(END_DT) FORMAT=DATETIME20. INTO :NEXT_REP_PERIOD_END FROM PRD WHERE YEARR = 2012; QUIT;

Online Status	Offline
Date Last Visited	‎09-01-2015 07:11 AM

Email notification on table structure and values change

Re: Create multiple datasets from single dataset

Create multiple datasets from single dataset

ODBC SQL server libname issue

print all combinations of upper and lowercase of string

Transpose based on a group

sorting character values which contains date and time

Get filenames modified in current month

Re: Can SAS Read email inbox ?

Can SAS Read email inbox ?

Re: Macro loop problem

List out all files in all sub directories

Re: concatenating all observations in single

concatenating all observations in single

Reading from macro variable to create dataset

Re: Macro values with new line characters

Macro values with new line characters

Control table and macros

More positional parameters found error when special characters present...

Create global macro variable inside a macro

Suggestion on approach for report

Re: Report with one header for subgroup

Report with one header for subgroup

PROC SQL : Create many variables from one variable using case

Macro with single quotes

Date in Macro Variable

Date in Macro Variable