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: 
tritringuyen
‎04-08-2022
  • 56 Posts
  • 14 Likes Given
  • 1 Solutions
  • 0 Likes Received

Re: How to rename a variable using filename in macro

by Quartz | Level 8 in SAS Data Management
‎07-10-2017 05:50 AM
‎07-10-2017 05:50 AM
Thank you very much for your response. The log is follows:   MPRINT(IMPORT): * Step 4: Rename variables; SYMBOLGEN: Macro variable I resolves to 2 MPRINT(IMPORT): data Dsn22; SYMBOLGEN: Macro variable I resolves to 2 MPRINT(IMPORT): set Dsn22; SYMBOLGEN: && resolves to &. SYMBOLGEN: && resolves to &. SYMBOLGEN: Macro variable I resolves to 2 SYMBOLGEN: Macro variable FILENAME2 resolves to C:\Users\Tri_Tri_nguyen\Desktop\Dead_final\01100.csv NOTE: Line generated by the macro variable "FILENAME2". 1763 WCC:\Users\Tri_Tri_nguyen\Desktop\Dead_final\01100.csv - 22 76 MPRINT(IMPORT): rename COL1=WCC:\Users\Tri_Tri_nguyen\Desktop\Dead_final\01100.csv; MPRINT(IMPORT): run; ERROR 22-322: Syntax error, expecting one of the following: a name, ;. ERROR 76-322: Syntax error, statement will be ignored. NOTE: The SAS System stopped processing this step because of errors. WARNING: The data set WORK.DSN22 may be incomplete. When this step was stopped there were 0 observations and 7 variables. WARNING: Data set WORK.DSN22 was not replaced because this step was stopped. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds     Basically, the variables are not renamed in the macro. I want to merge datasets after import and do some modifications. It is essential to rename a variable using filename. Please have a look at them and give me some advice. Thank you very much!   Cheers, Thierry   ... View more

How to rename a variable using filename in macro

by Quartz | Level 8 in SAS Data Management
‎07-10-2017 05:20 AM
‎07-10-2017 05:20 AM
Hello friends,   I want to rename a variable in macro by using filename (variable name = filename). I use the following code to import files and then use macro:   /* Read datafiles in folder*/ %let subdir=C:\Users\Tri_Tri_nguyen\Desktop\Dead_final\; filename dir "&subdir.*.csv "; data new; length filename fname $ 256; infile dir eof=last filename=fname; input ; last: filename=fname; run; /* sort data and remove duplicates-*/ proc sort data=new nodupkey; by filename; run; data null; set new; call symputx(cats('filename',_n_),filename); call symputx(cats('dsn',_n_),compress(scan(filename,-2,'\.') ,'ka')); call symputx('nobs',_n_); run; %put &nobs.;     %macro import; %do i=1 %to &nobs; /* Import for file No. 9 of board of directors */ proc import datafile="&&filename&i" out=dsn&i dbms=csv replace; getnames=no; run; run; .... .... data Dsn&i; set Dsn&i; rename COL1=WC&filename; /* I want to rename COL1 (in the imported file) using words "WC" and the name of the files I have imported above". For example, the imported file name is 12345, I want to rename COL1 into "wc12345". I got a lot of files like this (that is why I need a macro) */ run; ... ... %end; %mend import; %import   Everything in the macro works well, except rename issue. Thank you very much for your support!   Cheers, Thierry. ... View more

Re: Cumulative sum by three groups

by Quartz | Level 8 in SAS Data Management
‎07-07-2017 06:43 AM
‎07-07-2017 06:43 AM
Thank you very much! It is very useful! 🙂 ... View more

Cumulative sum by three groups

by Quartz | Level 8 in SAS Data Management
‎07-07-2017 05:48 AM
‎07-07-2017 05:48 AM
Hello,   I want to calculate cumulative sum by three variables. The following data give some ideas about what I want:   ISIN Fyear Group Values Want AAA 2010 1 10 10 AAA 2010 2 11 21 AAA 2010 3 12 33 AAA 2011 1 15 15 AAA 2011 2 18 33 AAA 2011 3 19 52 BBB 2010 1 20 20 BBB 2010 2 30 50 BBB 2010 3 40 90 BBB 2011 1 10 10 BBB 2011 2 15 25 BBB 2011 3 20 45   Basically, I want a variable "want"  which is the cumulative sum of "values" by ISIN, Fyear and group. I found some SAS codes from SAS community, but those codes are used to calculate the cumulative sum by one or two variables. I tried to modify and test those codes, but they did not work correctly. I think I need a new code. I wonder if anyone can help me a little bit. Thank you very much for your help!   Cheers, Thierry. ... View more

Re: Compound yearly return using monthly return

by Quartz | Level 8 in SAS Procedures
‎03-01-2017 11:08 AM
‎03-01-2017 11:08 AM
Thank you very much @mkeintz. I use your code. You are an excellent advisor! ... View more

Re: Compound yearly return using monthly return

by Quartz | Level 8 in SAS Procedures
‎03-01-2017 09:53 AM
‎03-01-2017 09:53 AM
Dear @mkeintz, thanks for your reply. I wrote the code to transform calendar year/month (year and month) to fiscal year/month (fyear and fmonth) based on FY_date (fiscal year end). Your prior code works very well with the data I posted previously. But after having fyear and fmonth, I do not rely on FY_date to compute yearly return.   new data looks like this (different sample, but variables are the same with the prior post):   gvkey monthyear year month mret FY_Date FY_year FY_month fmonth fyear 215 2005m1 2005 1 -0.07499 30/09/2005 2005 9 4 2005 215 2005m2 2005 2 0.32432 30/09/2005 2005 9 5 2005 215 2005m3 2005 3 0 30/09/2005 2005 9 6 2005 215 2005m4 2005 4 -0.18368 30/09/2005 2005 9 7 2005 215 2005m5 2005 5 0.175003 30/09/2005 2005 9 8 2005 215 2005m6 2005 6 0.170211 30/09/2005 2005 9 9 2005 215 2005m7 2005 7 0.072727 30/09/2005 2005 9 10 2005 215 2005m8 2005 8 0 30/09/2005 2005 9 11 2005 215 2005m9 2005 9 0 30/09/2005 2005 9 12 2005 215 2005m10 2005 10 0.016952 30/09/2005 2005 9 1 2006 215 2005m11 2005 11 -0.01667 30/09/2005 2005 9 2 2006 215 2005m12 2005 12 0 30/09/2005 2005 9 3 2006 215 2006m1 2006 1 0 30/09/2006 2006 9 4 2006 215 2006m2 2006 2 0.372884 30/09/2006 2006 9 5 2006 215 2006m3 2006 3 0.370371 30/09/2006 2006 9 6 2006 215 2006m4 2006 4 0.054053 30/09/2006 2006 9 7 2006 215 2006m5 2006 5 -0.06838 30/09/2006 2006 9 8 2006 215 2006m6 2006 6 0.08257 30/09/2006 2006 9 9 2006 215 2006m7 2006 7 0.593219 30/09/2006 2006 9 10 2006 215 2006m8 2006 8 -0.16489 30/09/2006 2006 9 11 2006 215 2006m9 2006 9 0.063693 30/09/2006 2006 9 12 2006 215 2006m10 2006 10 0.389222 30/09/2006 2006 9 1 2007 215 2006m11 2006 11 0.189654 30/09/2006 2006 9 2 2007 215 2006m12 2006 12 0.23913 30/09/2006 2006 9 3 2007 215 2007m1 2007 1 0.125731 30/09/2007 2007 9 4 2007 215 2007m2 2007 2 0.002611 30/09/2007 2007 9 5 2007 215 2007m3 2007 3 0.184896 30/09/2007 2007 9 6 2007 215 2007m4 2007 4 0.006594 30/09/2007 2007 9 7 2007 215 2007m5 2007 5 0.087336 30/09/2007 2007 9 8 2007 215 2007m6 2007 6 -0.04235 30/09/2007 2007 9 9 2007 215 2007m7 2007 7 0.037894 30/09/2007 2007 9 10 2007 215 2007m8 2007 8 0.103449 30/09/2007 2007 9 11 2007 215 2007m9 2007 9 -0.09375 30/09/2007 2007 9 12 2007 215 2007m10 2007 10 0.016227 30/09/2007 2007 9 1 2008 215 2007m11 2007 11 -0.00798 30/09/2007 2007 9 2 2008 215 2007m12 2007 12 -0.01207 30/09/2007 2007 9 3 2008 215 2008m1 2008 1 -0.07847 30/09/2008 2008 9 4 2008 215 2008m2 2008 2 0.051111 30/09/2008 2008 9 5 2008 215 2008m3 2008 3 0.078224 30/09/2008 2008 9 6 2008 215 2008m4 2008 4 0.066667 30/09/2008 2008 9 7 2008 215 2008m5 2008 5 0.049633 30/09/2008 2008 9 8 2008 215 2008m6 2008 6 0.102022 30/09/2008 2008 9 9 2008 215 2008m7 2008 7 -0.03834 30/09/2008 2008 9 10 2008 215 2008m8 2008 8 0 30/09/2008 2008 9 11 2008 215 2008m9 2008 9 -0.16943 30/09/2008 2008 9 12 2008 215 2008m10 2008 10 -0.272 30/09/2008 2008 9 1 2009 215 2008m11 2008 11 0.01923 30/09/2008 2008 9 2 2009 215 2008m12 2008 12 0.126685 30/09/2008 2008 9 3 2009     I wrote the following code:   data yearly_return; do until (last.fyear); set monthly_return; by gvkey fyear; if first.fyear then compound_ret=1; compound_ret=compound_ret*(1+mret); end; run; data yearly_return; set yearly_return; yret = compound_ret - 1; run; I manually checked the results of my code. It seems ok. What do you think? Am I correct?   If I want to use your code, how the code should be modified?   Thank you very much!   Best Regards,   Tri Tri ... View more

Re: Compound yearly return using monthly return

by Quartz | Level 8 in SAS Procedures
‎03-01-2017 05:19 AM
‎03-01-2017 05:19 AM
Thank you very much, @mkeintz. In fact, the fyear and month in my data is calendar year and month (not fiscal year). The only information about fiscal year given is FY_date (fiscal year end). I suppose that the code should be changed?   Thanks a lot! Thierry ... View more

Re: Compound yearly return using monthly return

by Quartz | Level 8 in SAS Procedures
‎02-26-2017 03:59 PM
‎02-26-2017 03:59 PM
Thanks @PGStats. FY_date of the same a company (gvkey) rarely changes. But I do not expect that it is fixed. However, different FY_dates for different companies are common. Is it clear?   Thank you very much! Thierry     ... View more

Compound yearly return using monthly return

by Quartz | Level 8 in SAS Procedures
‎02-26-2017 12:16 PM
‎02-26-2017 12:16 PM
Hi, I want to calculate compound yearly return for each company (gvkey) for each fiscal year (with year end is FY_date). The issue is that companies may have different year end. Monthly return is mret.   Basically, I want a new variable, say:   cum_mret = (1+ return of the first month of fiscal year)*(1+return of the second month of fiscal year)*...*(return of the 12th month of fiscal year).   Monthly returns used to calculate cum_mret are determined based on FY_date. For example, gvkey has the year end of 31/05/2004, then first month used to calculate cum_mret is 6/2003 and the 12th month used to calculate cum_mret is 5/2004.   Some months of some gvkey have missing values for mret.   Here are some observations. Could you please give me some suggestion about the code?   gvkey year month mret FY_Date 242 2003 1 -0.09524 31/05/2003 242 2003 2 0.342105 31/05/2003 242 2003 3 -0.01961 31/05/2003 242 2003 4 -0.04 31/05/2003 242 2003 5 0 31/05/2003 242 2003 6 0 31/05/2003 242 2003 7 0.125 31/05/2003 242 2003 8 0.388889 31/05/2003 242 2003 9 0.08 31/05/2003 242 2003 10 0.049383 31/05/2003 242 2003 11 0.117647 31/05/2003 242 2003 12 0.010526 31/05/2003 242 2004 1 0.229167 31/05/2004 242 2004 2 0.228814 31/05/2004 242 2004 3 0.186207 31/05/2004 242 2004 4 -0.00581 31/05/2004 242 2004 5 0.122807 31/05/2004 242 2004 6 -0.01563 31/05/2004 242 2004 7 -0.02646 31/05/2004 242 2004 8 -0.04076 31/05/2004 242 2004 9 0.121813 31/05/2004 242 2004 10 0.010101 31/05/2004 242 2004 11 0.045 31/05/2004 242 2004 12 0.253589 31/05/2004 242 2005 1 -0.05725 31/05/2005 242 2005 2 0.40081 31/05/2005 242 2005 3 -0.02601 31/05/2005 242 2005 4 0.017804 31/05/2005 242 2005 5 0.180758 31/05/2005 242 2005 6 -0.00988 31/05/2005 242 2005 7 -0.02494 31/05/2005 242 2005 8 -0.03964 31/05/2005 242 2005 9 0.159787 31/05/2005 242 2005 10 0.145809 31/05/2005 242 2005 11 -0.002 31/05/2005 242 2005 12 -0.01205 31/05/2005 ... View more

Re: Combine datasets

by Quartz | Level 8 in SAS Data Management
‎01-27-2017 07:17 AM
‎01-27-2017 07:17 AM
Thank you very much, in the future I will take into account this matter. ... View more

Re: Combine datasets

by Quartz | Level 8 in SAS Data Management
‎01-27-2017 07:02 AM
‎01-27-2017 07:02 AM
Dear @Astounding Thank you very much for your code. It is simple but it is all I need. The code is very helpful for me. Thanks.   Thierry ... View more

Re: Combine datasets

by Quartz | Level 8 in SAS Data Management
‎01-27-2017 06:25 AM
‎01-27-2017 06:25 AM
I need a code because I have thousands opairs of datasets which I need to combine one with its partner. Thank you very much!   Thierry ... View more

Combine datasets

by Quartz | Level 8 in SAS Data Management
‎01-27-2017 06:12 AM
‎01-27-2017 06:12 AM
Hello,   I want to combine two datasets (file attached). In general, the objective is to copy a specific string of texts in one dataset for all observations in another dataset.   Thank you very much!   Thierry   ... View more

Re: Create a macro to import and merge thousands CSV files into a sing...

by Quartz | Level 8 in SAS Data Management
‎01-26-2017 05:05 AM
‎01-26-2017 05:05 AM
Thank you very much! 🙂 ... View more

Re: Create a macro to import and merge thousands CSV files into a sing...

by Quartz | Level 8 in SAS Data Management
‎01-25-2017 07:19 PM
‎01-25-2017 07:19 PM
Thank you very much for the responses of all of you. Speical thanks to @Reeza for the code. I think it works. I already know how to use it, but still learn how to fix the issue around the length of variables. Maybe I will go back to the pointers to read datalines. Thanks. ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎04-08-2022 03:30 PM
Likes given to
Quick links