dear all
my data is in the following format
company_name | year | CA |
20 Microns Ltd. | 2011 | 958.9 |
20 Microns Ltd. | 2012 | 1185.3 |
20 Microns Ltd. | 2013 | 1097.5 |
20 Microns Ltd. | 2014 | 1341.1 |
20 Microns Ltd. | 2015 | 1505.3 |
20 Microns Ltd. | 2016 | 1631.1 |
20 Microns Ltd. | 2017 | 1557.9 |
20 Microns Ltd. | 2018 | 1679.5 |
20 Microns Ltd. | 2019 | 1893.7 |
20 Microns Ltd. | 2020 | 1957.2 |
20 Microns Ltd. | 2021 | 2040.1 |
3I Infotech Ltd. | 2011 | 9043.3 |
3I Infotech Ltd. | 2012 | 6329.7 |
3I Infotech Ltd. | 2013 | 6221.6 |
3I Infotech Ltd. | 2014 | 6976.8 |
3I Infotech Ltd. | 2015 | 6608.5 |
3I Infotech Ltd. | 2016 | 7162.7 |
3I Infotech Ltd. | 2017 | 7443.1 |
3I Infotech Ltd. | 2018 | 7101.5 |
3I Infotech Ltd. | 2019 | 7944.2 |
3I Infotech Ltd. | 2020 | 8165.2 |
3I Infotech Ltd. | 2021 | 11286.2 |
3M India Ltd. | 2011 | 4652.1 |
3M India Ltd. | 2012 | 5684.4 |
3M India Ltd. | 2013 | 8996.1 |
3M India Ltd. | 2014 | 9574.9 |
3M India Ltd. | 2015 | 7634.6 |
3M India Ltd. | 2016 | 10677.2 |
3M India Ltd. | 2017 | 15550.9 |
3M India Ltd. | 2018 | 17775.3 |
3M India Ltd. | 2019 | 15342.9 |
3M India Ltd. | 2020 | 17414.6 |
3M India Ltd. | 2021 | 19899.4 |
3P Land Holdings Ltd. | 2011 | 465.9 |
3P Land Holdings Ltd. | 2012 | 518 |
3P Land Holdings Ltd. | 2013 | 502.6 |
3P Land Holdings Ltd. | 2014 | 483.2 |
3P Land Holdings Ltd. | 2016 | 9.1 |
3P Land Holdings Ltd. | 2017 | 3.1 |
3P Land Holdings Ltd. | 2018 | 2.7 |
3Rd Rock Multimedia Ltd. | 2014 | 110.7 |
3Rd Rock Multimedia Ltd. | 2019 | 96.2 |
52 Weeks Entertainment Ltd. | 2018 | 388.4 |
52 Weeks Entertainment Ltd. | 2019 | 295.9 |
63 Moons Technologies Ltd. | 2011 | 10732.7 |
63 Moons Technologies Ltd. | 2012 | 14470.4 |
63 Moons Technologies Ltd. | 2013 | 16083.7 |
63 Moons Technologies Ltd. | 2014 | 13040.1 |
63 Moons Technologies Ltd. | 2015 | 17269 |
63 Moons Technologies Ltd. | 2016 | 7948.6 |
63 Moons Technologies Ltd. | 2017 | 7197.1 |
63 Moons Technologies Ltd. | 2018 | 13677.4 |
63 Moons Technologies Ltd. | 2019 | 13101.7 |
63 Moons Technologies Ltd. | 2020 | 13394.7 |
63 Moons Technologies Ltd. | 2021 | 13280.4 |
it is a unbalanced panel data of nearly 3,000 companies, from 2011 to 2021.
i have to create one year lag value of the variable "CA".
my output should be in the following format
company_name | year | CA | CA Lag |
20 Microns Ltd. | 2011 | 958.9 | |
20 Microns Ltd. | 2012 | 1185.3 | 958.9 |
20 Microns Ltd. | 2013 | 1097.5 | 1185.3 |
20 Microns Ltd. | 2014 | 1341.1 | 1097.5 |
20 Microns Ltd. | 2015 | 1505.3 | 1341.1 |
20 Microns Ltd. | 2016 | 1631.1 | 1505.3 |
20 Microns Ltd. | 2017 | 1557.9 | 1631.1 |
20 Microns Ltd. | 2018 | 1679.5 | 1557.9 |
20 Microns Ltd. | 2019 | 1893.7 | 1679.5 |
20 Microns Ltd. | 2020 | 1957.2 | 1893.7 |
20 Microns Ltd. | 2021 | 2040.1 | 1957.2 |
3I Infotech Ltd. | 2011 | 9043.3 | |
3I Infotech Ltd. | 2012 | 6329.7 | 9043.3 |
3I Infotech Ltd. | 2013 | 6221.6 | 6329.7 |
3I Infotech Ltd. | 2014 | 6976.8 | 6221.6 |
3I Infotech Ltd. | 2015 | 6608.5 | 6976.8 |
3I Infotech Ltd. | 2016 | 7162.7 | 6608.5 |
3I Infotech Ltd. | 2017 | 7443.1 | 7162.7 |
3I Infotech Ltd. | 2018 | 7101.5 | 7443.1 |
3I Infotech Ltd. | 2019 | 7944.2 | 7101.5 |
3I Infotech Ltd. | 2020 | 8165.2 | 7944.2 |
3I Infotech Ltd. | 2021 | 11286.2 | 8165.2 |
3M India Ltd. | 2011 | 4652.1 | |
3M India Ltd. | 2012 | 5684.4 | 4652.1 |
3M India Ltd. | 2013 | 8996.1 | 5684.4 |
3M India Ltd. | 2014 | 9574.9 | 8996.1 |
3M India Ltd. | 2015 | 7634.6 | 9574.9 |
3M India Ltd. | 2016 | 10677.2 | 7634.6 |
3M India Ltd. | 2017 | 15550.9 | 10677.2 |
3M India Ltd. | 2018 | 17775.3 | 15550.9 |
3M India Ltd. | 2019 | 15342.9 | 17775.3 |
3M India Ltd. | 2020 | 17414.6 | 15342.9 |
3M India Ltd. | 2021 | 19899.4 | 17414.6
|
please suggest me a SAS code to get the output in the above mentioned format
thanking you in advance
Good day
Kindly give the below a try. I've created dummy data to get the point across. I've also done it in smaller steps, to show you how to get to the final result. Whenever you need to lag something by a group, that group needs to be sorted, that's why there's a sort step. This can also be done in PROC SQL if you are more familiar with SQL.
DATA HAVE;
INPUT GROUP $ NUMBER;
DATALINES;
A 1
A 2
A 3
A 4
A 5
B 1
B 2
B 3
B 4
B 5
;
RUN;
PROC SORT DATA=HAVE OUT=SORT;
BY GROUP;
RUN;
DATA WANT;
SET SORT;
BY GROUP;
LAG_NUMBER = LAG1(NUMBER);
IF FIRST.GROUP THEN
LAG_NUMBER_CLEAN = .;
ELSE LAG_NUMBER_CLEAN = LAG_NUMBER;
RUN;
Use the LAG Function (unconditionally), and set the new variable to missing at first.company_name.
could you please provide one illustration of the SAS CODE for this purpose
It's simple; try it on your own, and let's see what you come up with.
The SAS communities are meant to assist people in their SAS life, not for doing other's work. If you want someone else to do your work for you, hire a consultant.
Good day
Kindly give the below a try. I've created dummy data to get the point across. I've also done it in smaller steps, to show you how to get to the final result. Whenever you need to lag something by a group, that group needs to be sorted, that's why there's a sort step. This can also be done in PROC SQL if you are more familiar with SQL.
DATA HAVE;
INPUT GROUP $ NUMBER;
DATALINES;
A 1
A 2
A 3
A 4
A 5
B 1
B 2
B 3
B 4
B 5
;
RUN;
PROC SORT DATA=HAVE OUT=SORT;
BY GROUP;
RUN;
DATA WANT;
SET SORT;
BY GROUP;
LAG_NUMBER = LAG1(NUMBER);
IF FIRST.GROUP THEN
LAG_NUMBER_CLEAN = .;
ELSE LAG_NUMBER_CLEAN = LAG_NUMBER;
RUN;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.