Hi everyone, Today I deal with replicating the same code for all sheets in Excel, just change the number of sheets, For example, the codes for sheets i (i=1,33) are the same. PROC IMPORT OUT= sheet2 DATAFILE= "C:\Users\pnguyen\Desktop\Argentina - delete.xlsm" DBMS=xlsx REPLACE; RANGE="Sheet2$A:AG"; GETNAMES=YES; RUN; proc sort data=sheet2; by Type; run; proc transpose data=sheet2 out=sheet2_out(rename=(COL1=sheet2 _NAME_=Year) drop=_label_); by Type; VAR '1988'N - '2019'N; transposing; run; PROC IMPORT OUT= sheet3 DATAFILE= "C:\Users\pnguyen\Desktop\Argentina - delete.xlsm" DBMS=xlsx REPLACE; RANGE="Sheet3$A:AG"; GETNAMES=YES; RUN; proc sort data=sheet3; by Type; run; proc transpose data=sheet3 out=sheet3_out(rename=(COL1=sheet3 _NAME_=Year) drop=_label_); by Type; VAR '1988'N - '2019'N; transposing; run; 1. Can you please suggest to me any way to wrap up or simplify the code rather than wrote such a group of code for 33 sheets in one excel file.   2. And a further question is: I have 65 files,each file contains 33 sheets like that, and the code for each file are totally the same, is there any way to work with 65 files by using SAS code rather than deal with each file manually?   Many thanks and warmest regards.   ... View more

Hi SAS community!   After discussing with you in the recent one month, I gradually feel the beauty of SAS, so I decide to change from using Stata to using SAS. However, I intend to learn the code relating to what I  am doing, and I think this is a possible way to do so. I have used the SAS EG quite a bit, so I have two main questions here:   1. Is there any chasm between SAS EG and SAS, because after a while using the SAS EG, I decide to move to use SAS, for it is better in the long term.   2. Do you know any document taught you to run OLS ( Y = x1 + x2 + x3....) with fixed effect and clustering standard error by using SAS? Because in Stata, we just only use a very easy command like: "xtreg y x1 x2 x3... i.year, fe cluster...." . I tried to google how to run OLS like that but I just received some incomplete guidance. I mean it is said I can use proc syslin to run the OLS, but I don't know how to add the "fixed effects and standard errors clustering" to the regression.   3. Sometimes, I also face the thing that I need to delete some observations containing some specific words for filtering data purpose, I think the reasonable way to do is to use "if... then ...delete", I am not sure if there is any other better way to do with this. Do you have a sample code for this question, say e.g, I want to delete all observation in data have in which the variable "abcd" (a character variable) contains the word "mnpd".   Thank you very much and have a nice weekend! P/S: Please let me know if I posted it to the wrong place.   ... View more

Hi SAS community, I have the input as below: Offtrack a little bit, this one is obtained after using the "sum" statement in proc means.   Back to my question, my column of interest is agg_amh. What I want is to have an average of all rows containing number values but not the first row (because the first row is the aggregate number already). In other words, I am asking how to count the number of rows containing "number" in column "agg_amh" (but the row (1)), then I will use the aggregate number 0.0003195096 divide to this number to get the average number. In sum, my ulterior motive is to get the number of rows that contain number but the first row, in this case, we have 17 rows from row 6th to row 22nd. Then I want to divide 0.0003195096 to 17. The final number that I want is 0.0003195096/17=0.0000187946.   Many thanks.   ... View more

Hi SAS community,   I have the data of annual market capitalization (ann_mar_cap) of some firms in some years. I also sorted the datafile by year already (column Type indicates distinc firms). Type year obs_amihud amihud ann_mar_cap 320864 1991 217 0.0000298897 . 321406 1991 120 0.0001126239 . 320864 1992 216 0.0000513143 . 321406 1992 72 0.0002507506 . 320864 1993 185 0.0001213274 . 321406 1993 11 0.0004665429 . 320864 1994 143 0.0001321269 99236 321406 1994 7 0.0006057594 104592 321716 1994 3 0.0000357925 131664 And I also attached the dataset as below. My columns of interests are amihud and ann_mar_cap. what I need is to calculate the value-weighted amihud.  To calculate it, I need to calculate the sum of ann_mar_cap for all firm( column Type) each year of this country, after that, I divide the ann_market_cap of each firm for the ann_mar_cap of this country at year t-1. After that, I need to multiple the amihud with this quotient. The promising result is as below. Type year obs_amihud amihud ann_mar_cap total_cap value_w 320864 1991 217 0.0000298897 . . . 321406 1991 120 0.0001126239 . . . 320864 1992 216 0.0000513143 . . . 321406 1992 72 0.0002507506 . . . 320864 1993 185 0.0001213274 . . . 321406 1993 11 0.0004665429 . . . 320864 1994 143 0.0001321269 99236 335492 . 321406 1994 7 0.0006057594 104592 335492 . 321716 1994 3 0.0000357925 131664 335492 . 131712 1995 0 . . 1461874 . 320356 1995 45 0.0000552666 736183 1461874 0.000121 320599 1995 0 . 211901 1461874 . 320678 1995 0 . 329055 1461874 . 320864 1995 21 0.0001192145 95202 1461874 0.000034 321406 1995 0 . 89533 1461874 . 321716 1995 4 0.0001505341 94929 1461874 0.000042 321718 1995 0 . . 1461874 . 321721 1995 0 . . 1461874 . Can you please suggest me the code of doing so? Many thanks.   ... View more

Hi community,   I have two indistinguishable sets of code for solving data for two different variables (the codes for each set includes: proc sort, proc means, merge, proc rank, and trim data), the order of the lines of code and everything is similar between these two sets of codes (the difference is only the name of the variable).   I can get to the goal with these two sets of code, but since I saw the aesthetic SAS, I believe that there should be one way to merge these codes (especially they are resemblant to each other).  I am wondering if we can merge these two sets of code together. I am wondering if you can suggest me the way that I can search or follow.   Thanks in advance.   ... View more

Hi everyone, When try to merge two dataset that has some distinct Type in common, I face the problem with the result. even try too out at the log file, I also cannot find where the error comes from. I am wondering if you can have a look and give me a suggestion or else.   I have the dataset final (called a). And I have another raw dataset (called have) What I want is to match observation in raw data (dataset have) associated with the dataset final based on "Type" and "year" of the dataset final as below (we call dataset want): By doing this, I use the code as below: proc sort data=final; by Type; run; proc sort data=have; by Type; run; proc sql; create Table want as select a.*,b.* from final as a, have as b where (a.Type = b.Type and a.year=b.year); quit; And the results as the picture above, but when I check the log, it shows two warnings like that, I am wondering if there is anything wrong in my code or there is anything that I did not concern enough. proc sql; 101 create Table merge_ as 102 select a.*,b.* 103 from step4 as a, merge_9_10 as b 104 where (a.Type = b.Type and a.year=b.year); WARNING: Variable Type already exists on file WORK.MERGE_. WARNING: Variable year already exists on file WORK.MERGE_. Thanks in advance!   ... View more