cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
mahesh146
‎06-11-2018
  • 15 Posts
  • 1 Likes Given
  • 4 Solutions
  • 5 Likes Received

Re: Using PROC Template Tagset for XML Export

by Obsidian | Level 7 in SAS Programming
‎06-08-2018 05:15 AM
‎06-08-2018 05:15 AM
Thanks for the link. I had already explored that one. 🙂   The problem is that we need XML file in specific format with more than 4 level of hierarchies which can be achieved through custom tagset. So looking for the answers on PROC Template. ... View more

Using PROC Template Tagset for XML Export

by Obsidian | Level 7 in SAS Programming
‎06-08-2018 04:27 AM
‎06-08-2018 04:27 AM
Hi Guys,   I am looking into use of PROC Template with Define Tagset  option for exporting SAS datasets into XML files. The online materials are quite explanatory in terms the commands to be used and syntax to be followed for the same.  However, I am unable to get below things. 1. How would the programmer decide the sequence of events to be created? 2. Is events name just like variable name where user can use any name as per convenience? If not, is there any online resource where I can find the event names and their use? 3. How events gets executed and mapped to the dataset to print values in XML?   I look forward to your responses.   Thanks, Mahesh ... View more

Re: function

by Obsidian | Level 7 in SAS Programming
‎06-04-2018 06:30 AM
1 Like
‎06-04-2018 06:30 AM
1 Like
Please check the below: PROC IMPORT DATAFILE='/folders/myfolders/sasuser.v94/flat files/test/department.csv' DBMS=CSV OUT=TEMP REPLACE ; RUN; DATA TEMP; SET TEMP; Last_Name=SCAN(Name,1,','); Tmp_fst_nm=SCAN(Name,2,','); Fst_Nm= SUBSTR(Tmp_fst_nm,1,LENGTH(Tmp_fst_nm)-2); DROP Tmp_fst_nm; RUN; ... View more

Re: How to add missing columns in existing dataset

by Obsidian | Level 7 in SAS Programming
‎06-04-2018 04:27 AM
1 Like
‎06-04-2018 04:27 AM
1 Like
Please check if below code helps.   %LET FolderPath= /folders/myfolders/sasuser.v94/test; %MACRO Excel_Load(file); PROC IMPORT DATAFILE="&folderpath/&file..xlsx" DBMS=XLSX REPLACE OUT=TEMP ; RUN; DATA TEMP; SET TEMP; ARRAY EXT{9} $; DO i=1 to 9; IF EXT{i}=' ' THEN EXT{i}='n/a'; END; DROP i; RUN; PROC PRINT DATA=TEMP; TITLE "&file..xlsx"; RUN; %LET tmp_final_id= %SYSFUNC(OPEN(TEMP_FINAL)); %LET rc=%SYSFUNC(CLOSE(&tmp_final_id)); %PUT 'tmp_final_id='&tmp_final_id 'rc='&rc;   %IF &tmp_final_id=0 %THEN %DO; DATA TEMP_FINAL; SET TEMP; RUN; %END; %ELSE %DO; DATA TEMP_FINAL; SET TEMP_FINAL TEMP; RUN; %END; %MEND Excel_Load; PROC DATASETS NOLIST; DELETE TEMP_FINAL; RUN; %Excel_Load(ST1); %Excel_Load(ST2); %Excel_Load(ST3); ... View more

Re: Value of variable depending on one entry of the data table

by Obsidian | Level 7 in SAS Programming
‎06-04-2018 02:11 AM
‎06-04-2018 02:11 AM
Please check below code. DATA TEMP; INPUT Code year value; CARDS; 10 2015 375 10 2016 640 10 2017 710 20 2014 0 20 2015 125 20 2016 950 20 2017 0 ; RUN; PROC PRINT DATA=TEMP; TITLE 'Source Table'; RUN; PROC SQL; CREATE TABLE TEMP1 AS SELECT Code, Year, MIN(value) as Min_Val, CASE WHEN calculated Min_Val=0 THEN 0 ELSE value END as Final_Value FROM TEMP GROUP BY Code ; QUIT; DATA TEMP; SET TEMP1; RENAME Final_value=Value; DROP Min_Val; RUN; PROC PRINT DATA=TEMP; TITLE 'Final Table'; RUN; ... View more

Re: Keeping only part of a variable that exists after the first space

by Obsidian | Level 7 in SAS Programming
‎06-04-2018 01:46 AM
‎06-04-2018 01:46 AM
Final_Col in below dataset has the target output. DATA TEMP; INPUT Sports $1-35; Fst_Col= Length(Scan(STRIP(Sports),1)); Final_col= SUBSTR(Sports,Fst_col+1); DROP Fst_Col; CARDS; 11 Foot Ball 112 Soccer and golf 2345 basketball 12 Table tennis ; RUN; ... View more

Re: sumif and a countif in sas

by Obsidian | Level 7 in SAS Programming
‎06-01-2018 06:55 AM
‎06-01-2018 06:55 AM
Please try below solution. DATA TEMP; infile cards; input age1-age7 probability; cards; 0 0 1 0 0 0 0 0.037316109 0 0 1 0 0 0 0 0.012645846 0 0 1 0 0 0 0 0.030745011 1 0 0 0 0 0 0 0.109232968 0 1 0 0 0 0 0 0.121137734 0 0 0 1 0 0 0 0.064422562 0 0 1 0 0 0 0 0.030978301 0 0 1 0 0 0 0 0.030990311 0 0 1 0 0 0 0 0.15763914 1 0 0 0 0 0 0 0.05447492 1 0 0 0 0 0 0 0.032326732 0 0 1 0 0 0 0 0.100191375 0 0 1 0 0 0 0 0.015438986 1 0 0 0 0 0 0 0.026323937 0 1 0 0 0 0 0 0.069376926 0 1 0 0 0 0 0 0.030861443 0 0 0 0 1 0 0 0.019696911 ; run; PROC SORT DATA=TEMP; BY probability; PROC TRANSPOSE DATA=TEMP OUT=TEMP_1 NAME= Age_Col; BY probability; VAR Age:; RUN; PROC SQL; SELECT Age_col, SUM(COL1) as Total_Count, SUM( CASE WHEN COL1=1 THEN probability ELSE 0 END ) as Sum_Prob FROM TEMP_1 GROUP BY Age_col ; QUIT; ... View more

Re: How to count number of older siblings and the number of younger si...

by Obsidian | Level 7 in SAS Programming
‎06-01-2018 03:37 AM
1 Like
‎06-01-2018 03:37 AM
1 Like
Hi, Please check if this helps. DATA TEMP; INPUT Fam_id Sdt_ID Sch $ Grade $ DOB DOB6th Age_Diff; INFORMAT DOB DOB6th mmddyy8.; FORMAT DOB DOB6th mmddyy10.; CARDS; 90021 103699 . 4th 07/24/2004 01/26/2002 2.49 90021 127945 RG 6th 01/26/2002 01/26/2002 0 90021 149229 . 2nd 10/28/2005 01/26/2002 3.75 90053 109831 RC 6th 08/27/2002 08/27/2002 0 90053 122779 . 5th 08/28/2003 08/27/2002 1 90053 124617 . 8th 05/07/2000 08/27/2002 -2.31 90097 145616 . 4th 06/06/2004 12/20/2001 2.46 90097 164264 RC 6th 12/20/2001 12/20/2001 0 90112 147688 . 7th 10/11/2000 02/23/2002 -1.37 90112 171989 . 9th 06/27/1999 02/23/2002 -2.66 90112 197925 RG 6th 02/23/2002 02/23/2002 0 ; RUN; PROC SQL; CREATE TABLE TEMP_CAL AS Select COALESCE(A.Fam_id,B.Fam_id) as Fam_id, /*SAS Functions are also applicable in PROC SQL queries including normal SQL functions*/ CASE WHEN A.Old_Student_Count=. THEN 0 ELSE A.Old_Student_Count END as Old_Student_Count, CASE WHEN B.Young_Student_Count=. THEN 0 ELSE B.Young_Student_Count END as Young_Student_Count FROM ( Select Fam_id, Count(Distinct Sdt_ID) as Old_Student_Count FROM TEMP WHERE Age_Diff<0 GROUP BY Fam_id ) A FULL JOIN ( Select Fam_id, Count(Distinct Sdt_ID) as Young_Student_Count FROM TEMP WHERE Age_Diff>0 GROUP BY Fam_id ) B ON A.Fam_id=B.Fam_id ; QUIT; DATA TEMP_FINAL; IF _n_=1 THEN DO; IF 0 THEN SET TEMP_CAL; Declare Hash h1(dataset:'TEMP_CAL'); h1.definekey('Fam_id'); h1.definedata('Young_Student_Count','Old_Student_Count'); h1.definedone(); END; SET TEMP; h1.find(key:Fam_id); IF Grade NE '6th' THEN DO; Young_Student_Count=.; Old_Student_Count=.; END; RUN; PROC PRINT DATA=TEMP_FINAL; TITLE 'Final Table'; RUN; ... View more

Re: Summing over intervals of time within a group

by Obsidian | Level 7 in SAS Programming
‎05-31-2018 05:59 AM
‎05-31-2018 05:59 AM
Please try this code and see if it helps.     DATA TEMP; INPUT Company_id Year Earnings; CARDS; 123 2000 5.5 123 2001 6.7 123 2002 7.1 123 2003 7.2 123 2004 6.9 123 2005 8.1 456 2000 15.5 456 2001 16.7 456 2002 17.1 456 2003 17.2 456 2004 16.9 456 2005 28.1 ; RUN; DATA TEMP_C; SET TEMP; RENAME Company_id=New_Company_id Year=New_Year Earnings=New_Er ; RUN; DATA TEMP1; IF _n_=1 THEN DO; IF 0 THEN SET TEMP; DECLARE HASH h1(multidata:'y'); h1.DEFINEKEY('Company_id','Year'); h1.DEFINEDATA('Earnings'); h1.DEFINEDONE(); END; DO UNTIL(LAST.Company_id); SET TEMP; BY Company_id; h1.add(); END; DO UNTIL(LAST.New_Company_id); SET TEMP_C; BY New_Company_id; ARRAY nxt_5{5} _temporary_; DO i=1 to 5; TEMP_Year= New_Year+i; rc=h1.find(key:New_Company_id,key:TEMP_Year); IF rc=0 THEN nxt_5{i}=Earnings; ELSE LEAVE; END; TOTAL=SUM(OF nxt_5{*}); Std_dev= STD(OF nxt_5{*}); IF i<6 THEN DO; FINAL_TOTAL=.; FINAL_STD_DEV=.; END; ELSE DO; FINAL_TOTAL=TOTAL; FINAL_STD_DEV=Std_dev; END; OUTPUT; END; h1.clear(); RUN; DATA TEMP_FINAL; SET TEMP1(DROP= i rc Total Std_dev TEMP_Year Company_id Year Earnings); RENAME New_Company_id=Company_id New_Year=Year New_Er=Earnings FINAL_TOTAL= Sum_Next_5_years FINAL_STD_DEV= Standard_Dev_Next_5_years ; RUN; PROC PRINT DATA= TEMP_FINAL; RUN; PROC DATASETS NOLIST; DELETE TEMP1; RUN; ... View more

Re: Need to count number of the session by using retain, (please help ...

by Obsidian | Level 7 in SAS Programming
‎05-30-2018 02:46 AM
‎05-30-2018 02:46 AM
Please try this code and refer to Revised_Session_Count column. Please confirm if it helps.   FILENAME F1 '/folders/myfolders/sasuser.v94/flat files/Session Calc.xlsx'; PROC IMPORT DATAFILE=F1 OUT=TEMP_T DBMS=XLSX REPLACE; DATA TEMP; SET TEMP_T; FORMAT DT_NEW datetime.; KEEP User_Catg_ID session_T DT_New;* D_dt date; DT_New=INT(INPUT(DT,datetime.)); *INFORMAT DT datetime16.; /*INFORMAT statement couldn't read DT as datetime value. Not sure why. SO used INPUT() with same informat and it worked just fine*/ RENAME DT_NEW=DT; RUN;   /*----Deleting TEMP_T dataset---*/ PROC DATASETS NOLIST LIB=work; DELETE TEMP_T; RUN; PROC SORT DATA=TEMP; BY User_Catg_ID DT; DATA TEMP_1; SET TEMP; BY User_Catg_ID; Prev_val=LAG(DT); IF NOT FIRST.User_Catg_ID THEN TIME_DIFF=DT-Prev_val; /*NOTE: Using LAG(DT) directly in place of Prev_Val returns some weird results in few scenarios*/ ELSE TIME_DIFF=0; RETAIN Revised_Session_Count 1; IF FIRST.User_Catg_ID THEN Revised_Session_Count=1; ELSE IF TIME_DIFF > (30*60) and NOT FIRST.User_Catg_ID THEN Revised_Session_Count+1; DROP Prev_Val; RUN; PROC PRINT DATA=TEMP_1; RUN; ... View more

Re: Need to count number of the session by using retain, (please help ...

by Obsidian | Level 7 in SAS Programming
‎05-30-2018 01:41 AM
‎05-30-2018 01:41 AM
Can you please share the sample format in which you need the final dataset to look like? ... View more

Re: Replace string with another string

by Obsidian | Level 7 in SAS Programming
‎05-29-2018 10:15 AM
2 Likes
‎05-29-2018 10:15 AM
2 Likes
DATA TEMP; word = "I am a student in a college in a city in INDIA to study masters"; word_len= LENGTH(STRIP(word)); fst_pos= FINDW(STRIP(word),'in'); snd_pos= FINDW(STRIP(word),'in',fst_pos+1); new_str_part1= substr(STRIP(word),1,snd_pos-1); new_str_part2= 'from'; new_str_part3= substr(STRIP(word),snd_pos+2); new_word= CAT(new_str_part1,new_str_part2,new_str_part3); DROP word_len fst_pos snd_pos new_str_part:; run;   PROC PRINT DATA=TEMP; RUN; ... View more

Re: Calculating number of hours from midnight

by Obsidian | Level 7 in SAS Programming
‎05-29-2018 01:51 AM
‎05-29-2018 01:51 AM
DATA TEMP; INPUT D_TIME:time.; FORMAT D_TIME midnight timeampm. Time_diff_midnight hhmm8.; hr=hour(D_TIME); %LET mid_night= %SYSFUNC(HMS(24,00,00)); midnight= &mid_night; IF hr>=12 THEN Time_diff_midnight= D_TIME-&mid_night; ELSE Time_diff_midnight=D_TIME; CARDS; 12:30pm 11:10am 12:10am 5:10am 2:30pm 6:30pm 11:58pm 12:01am ; RUN; PROC PRINT DATA=TEMP; ... View more

Re: how to populate a date if day or month or year missing ?

by Obsidian | Level 7 in SAS Programming
‎05-22-2018 04:05 AM
‎05-22-2018 04:05 AM
DATA TEMP(DROP=TEMP_MONTH TEMP_DATE); input sdate $10.; Year=SCAN(sdate,1,'-'); TEMP_Month=SCAN(sdate,2,'-'); TEMP_Date=SCAN(sdate,3,'-'); IF TEMP_MONTH NE . THEN MONTH=TEMP_MONTH; ELSE MONTH='01'; IF TEMP_Date NE . THEN DATE=TEMP_Date; ELSE DATE='01'; FINAL_DATE= MDY(MONTH,DATE,YEAR); FORMAT FINAL_DATE worddate15.; cards; 2009-10-14 2010-12-24 2001-03 2006-06 2012 2002-03-27 2003-02-03 ; RUN; ... View more

Re: merge by date and date range

by Obsidian | Level 7 in SAS Programming
‎05-22-2018 03:49 AM
‎05-22-2018 03:49 AM
DATA TEMP; INPUT ID date Nation $; INFORMAT DATE mmddyy10.; FORMAT DATE date9.; DATALINES; 1 02/25/2016 USA 2 05/12/2015 UK 3 11/05/2018 INDIA 1 06/20/2018 Spain ; RUN; DATA TEMP_RANGE; INPUT ID ST_DT END_DT VAL; INFORMAT ST_DT END_DT ddmmyy10.; FORMAT ST_DT END_DT date9.; CARDS; 1 01/01/2015 31/12/2017 100 1 01/01/2018 31/12/2018 200 2 25/10/2014 31/12/2015 50 3 15/05/2015 30/11/2018 150 ; RUN;   PROC SQL; CREATE TABLE TEMP_FINAL AS SELECT A.*, B.ST_DT, B.END_DT, B.VAL FROM TEMP A LEFT JOIN TEMP_RANGE B ON A.ID=B.ID AND B.ST_DT<=A.DATE<=B.END_DT ; TITLE 'Final Table'; SELECT * FROM TEMP_FINAL; QUIT; ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎06-11-2018 08:38 AM
Likes given to
View All
Likes from
Quick links