Use SAS function Propcase() to streamline Google Contacts

You might think I am crazy…but I have been using this macro for a long time
to fix some contacts in my Google Contacts…I get a little irritated when I
can’t find a particular person by email…so I wrote this macro…

This macro takes for Input a .csv file that is
exported from Google Contacts and outputs a file that is ready to be imported
to Google Contacts….often I wanted to have Names in the proper case…

Try it yourself and let me know if it needs any tweaks…

 

%macro
organizeGoogleContacts(infile,outfile);

 

/*Import your
contacts into SAS */

data googlegroups;

infile
"&infile"
dlm=',' dsd lrecl=32767
firstobs=2;

length Name      GivenName       AdditionalName
Family_Name    Yomi_Name      Given_Name_Yomi        
   Additional_Name_Yomi
Family_Name_Yomi      
Name_Prefix    
Name_Suffix    
Initials   Nickname          Short_Name            Maiden_Name   Birthday            Gender Location           Billing_Information         Directory_Server           Mileage            Occupation       Hobby 
Sensitivity         Priority  Subject Notes   Group_Membership       E_mail_1___Type            E_mail_1___Value         Phone_1___Type           Phone_1___Value          Phone_2___Type           Phone_2___Value            Phone_3___Type           Phone_3___Value          Organization_1___Type  Organization_1___Name            Organization_1___Yomi_Name   Organization_1___Title   Organization_1___Department    Organization_1___Symbol            Organization_1___Location        Organization_1___Job_Description $ 40;

input Name       GivenName       AdditionalName
Family_Name    Yomi_Name      Given_Name_Yomi           
Additional_Name_Yomi Family_Name_Yomi       Name_Prefix     Name_Suffix     Initials  
Nickname          Short_Name           
Maiden_Name  
Birthday            Gender
Location           Billing_Information         Directory_Server           Mileage            Occupation       Hobby 
Sensitivity         Priority  Subject Notes   Group_Membership       E_mail_1___Type            E_mail_1___Value         Phone_1___Type           Phone_1___Value          Phone_2___Type           Phone_2___Value            Phone_3___Type           Phone_3___Value          Organization_1___Type  Organization_1___Name            Organization_1___Yomi_Name   Organization_1___Title   Organization_1___Department    Organization_1___Symbol            Organization_1___Location        Organization_1___Job_Description;

run;

 

/* For unnamed
contacts use the Email username as the Name */

data names;

set googlegroups;

if name=''
and E_mail_1___Value ne '' then name=propcase(lowcase(scan(E_mail_1___Value,1,'@')));/*Format
the names with camelcase*/

else name=propcase(upcase(name));/*Format
the names with camelcase*/

run;

 

/* Remove any
duplicates by name/email/phone contacts */

proc sort
data=names out=googlecontacts nodupkey;

by name
E_mail_1___Value Phone_1___Value;

where
E_mail_1___Value ne '' or Phone_1___Value ne '';

run;

 

/* Many a times
one contact may have been noted down with email contacts only */

proc sort data=googlecontacts out=email nodupkey;

by name;

where
E_mail_1___Value ne '';

run;

 

/* Many a times
one contact may have been noted down with Phone number only */

proc sort data=googlecontacts out=phone nodupkey;

by name;

where Phone_1___Value
ne '';

run;

 

/* Merge the
email and Phone contacts by name to get the email and phone on to one record */

data merged;

merge email
phone;

by name;

run;

 

/* Export it out
to Google csv once again... */

PROC EXPORT
DATA= WORK.Merged

            OUTFILE="&outfile"

            DBMS=CSV REPLACE;

RUN;

 

/* Delete all
Work datasets proactively */

proc datasets
lib=work;

delete _all_;

quit;

%mend
organizeGoogleContacts;

 

%let
infile=C:\Documents and Settings\SASTechies\Desktop\google.csv;

%let
outfile=C:\Documents and Settings\SASTechies\Desktop\GoogleContacts.csv;

 

%organizeGoogleContacts(&infile,&outfile);

 

Export data from SAS into Microsoft Excel

There are a number of ways to export data from SAS into Microsoft Excel. However, very few allow for exporting into custom-formatted spreadsheets such as those demanded for specific reports, in which explicit attributes such as font sizes and column widths are required. Traditionally, Dynamic Data Exchange (DDE) was the only way to do this. Now there are newer methods for this which are considered superior…

Various methods are —

1. Proc Export / SAS Export wizard
2. Dynamic Data Exchange (DDE)
3. ODS CSV / HTML xls file route…create a .csv file and open with Excel
4. SAS to Excel via XML
5. Libname with xls
6. Using Display manager command Dexport to create a csv file

1. Proc Export / SAS Export wizard

2. Dynamic Data Exchange (DDE)

Export SAS datasets to Excel with DDE – 

3. ODS CSV / HTML xls file route…create a .csv file and open with Excel

4. SAS to Excel via XML

Export SAS datasets to Excel via XML –

5. Libname with xls

Export SAS datasets to Excel -

6. Using Display manager command Dexport to create a csv file….The Display manaager (DM Dexport) command invokes the External File Interface of SAS…

dm "dexport sashelp.class 'C:\class.csv' ";

dm "dexport sashelp.class 'C:\class.xls' ";

Interesting References….

Excellent Ways of Exporting SAS Data to Excel

Finding the nth business day of a given month and year

This sample shows a macro version and the data step version that can be used to find the nth business day of a given month and year.

This example uses the INTNX function to increment the date, time, or datetime value by a given interval or intervals.
http://support.sas.com/onlinedoc/913/getDoc/en/lrdict.hlp/a000212700.htm

This macro is a modified version of the Source: http://support.sas.com/kb/37/632.html

/* This code creates the macro variable DAY that is the nth day of the month. The macro variable is in the SAS date format

*/

%macro test1(checkday,month,year);

%global day;

%let date=%str(%'01)%upcase(%substr(&month,1,3))&year%str(%'d);

%put date=&date;

%let nthday=0; 

%let n=0;   

%do %until(&nthday=&checkday); 

/* create a variable whose value is the first day of the month*/  

%let begin_month=%sysfunc(intnx(month,&date,0,B)); 

/* increment the date by 1 day at a time*/  

%let day=%sysfunc(intnx(day,&begin_month,&n)); 

/* determine the day of the week*/  

%let weekday=%sysfunc(weekday(&day));

/* if the day of the week is not Saturday or Sunday then increment 

nthday plus 1*/ 

%if &weekday ne 7 and &weekday ne 1 %then %let nthday=%eval(&nthday+1);

%let n=%eval(&n+1);

%end;

/* checks the DAY macro variable by writing it to the log in the DATE9.

format*/

%put %sysfunc(putn(&day,date9.));

%mend; 

 

%test1(5,SEP,2010)   

 

%macro test2(checkday,month,year););

  %global day;

data test;  

  date=input(compress('01'||substr("&month",1,3)||&year),date9.);

  put date=;

  nthday=0;

  n=0; 

  do until(nthday=&checkday);

  begin_month=intnx('month',date,0,'B'); 

  day=intnx('day',begin_month,n);

  weekday=weekday(day);

  if weekday ne 7 and weekday ne 1 then nthday=nthday+1; 

  n+1; 

  end; 

  drop begin_month weekday date n; 

run; 

 

proc print;

  format day date9.; 

run; 

%mend; 

%test2(8,april,2010)

Patron segmentation on SAS VA

SAS macro to split a macro variables values to multiple macro variables values and also get the split count into another variable

And also there could be other instances where you need to do the opposite i.e. split a macro variable values list into individual pieces and get them into a series of macro variable values. One can use the below macro for such a requirement.

/**************************************************************************************************************

* Macro_Name:   split_macrovalue                                                                       

*

* Purpose: this macro is used to split a macro and return series of macro variables

*         

*                                                                                                             

* Usage: %split_macrovalue(macvar,prefix,splitcnt,dlm=' ');

*

* Input_parameters: macvar - input macro var

*                   prefix - prefix of the macro vars to be created in sequence order...

*                   splitcnt - name of the macro vars to store the length of the macro variable

*                   dlm=     - delimiter in the string (default is comma i.e. ,)

*                                                                                                             

* Outputs:  None.                                                                                              

*                                                                                                             

* Returns:  None  

*

* Example:

*                                                     %let str1=he she me to 3 "some";

*                                                     %SplitMacroValue(str1,pre,strcnt,dlm=' ');

*                                                    

*                                                     %put pre1=&pre1 pre2=&pre2 pre3=&pre3 pre4=&pre4 pre5=&pre5 pre6=&pre6 ;

*                                                     %put strcnt=&strcnt;

*                                                                                                             

* Modules_called: None

*

* Maintenance_History:                                                                                       

*-------------------------------------------------------------------------------------------------------------*

*  Date:      |   Who:        |  Description:                                                                 *

*-------------------------------------------------------------------------------------------------------------*

* 12/14/2011  | Sharad        | Initial creation.                                                          *

*

*-------------------------------------------------------------------------------------------------------------*

* HeaderEnd:                                                                                                  *

**************************************************************************************************************/

%macro split_macrovalue(macvar,prefix,splitcnt,dlm=comma,printvalue=N);

%put Start macro split_macrovalue(&macvar,&prefix,&splitcnt,&dlm);

 %if %upcase(&dlm) eq COMMA %then %do; %let dlm=,; %end;

 %else %if %upcase(&dlm) eq PIPE %then %do; %let dlm=|; %end;

 %else %if %upcase(&dlm) eq SPACE %then %do; %let dlm=%str( ); %end;

 %global &splitcnt; /*create the global variable for storing the splitcount*/

 %let num=1; /*initialize a counter variable*/  

 %global &prefix# /*create the global variable for storing the first split*/

 %let &prefix&num=%scan(%superq(&macvar),%superq(num),%superq(dlm)); /*Scan the first value*/

/* %put value here is &&&prefix#*/

/* Remove the comment's to check the values */

      %if %index(%superq(&macvar),%superq(dlm)) gt 0 %then

      %do;

       

       %do %while(%length(&&&prefix&num) gt 0);

            %let &splitcnt=# /*Store the split count to the macro variable reqd*/            

          %let num=%eval(&num + 1);/*increment the counter*/        

          %global &prefix# /*create the global variable*/

          %let &prefix&num=%scan(%superq(&macvar),%superq(num),%superq(dlm)); /*scan the next value*/

/*        %put The next value is &prefix&num=&&&prefix# */

        %end;

      %end;

      %else

      %do; 

              %let &splitcnt=# /*Store the split count to the macro variable reqd*/ 

      %end;  

      %put The number of values are &&&splitcnt;

      /* Remove the comment's to check the values */

      %if &printvalue=Y %then

      %do;

          

            %do i=1 %to &&&splitcnt;

           %put &prefix&i = &&&prefix&i;

            %end;  

      %end;

%put End macro split_macrovalue;

%mend split_macrovalue;

These macro calls below take the macro variable ‘gender’ with a value list and populate them into a series of macro variables with a prefix ‘gen’…Please note the variations of the delimiters in each of the macro invocations.

 

options mprint mprintnest mlogic mlogicnest;

%let gender = 'F','M';

%split_macrovalue(gender,gen,cnt,dlm=comma,printvalue=Y);

%let gender = 'F'|'M';

%split_macrovalue(gender,sgen,scnt,dlm=pipe,printvalue=Y);

%let gender = 'F' 'M';

%split_macrovalue(gender,tgen,tcnt,dlm=space,printvalue=Y);

I hope these macros help you in simplifying your situations ….

Please let me know if you have any questions.